Microsoft Power Query for Excel enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more. Power Query also provides you with the ability to search for public data from sources such as Wikipedia.
Questions tagged [powerquery]
5627 questions
1
vote
1 answer
Power Query: UDF, change the data type of the result
I'm doing my very first steps in Power Query UDFs.
My goal is the function result like (Excel) DATE (). This is how I reach the goal:
() => Date.From (DateTime.LocalNow ())
The result is correct, but the data type is "any". I want the data type to…
1
vote
2 answers
How to ignore Excel Query Errors
I am using Excel's Power Query for data analysis. In the file that contains the original data, there are about 15 columns. The query is only pulling data based on one of the columns. For example, the column contains either 'A' or 'B'. I want all…
Brian Phair
- 328
- 4
- 12
1
vote
1 answer
Function.InvokeAfter trouble
I'm trying to delay each call to StockData with 5 seconds in order to get by an API call restriction. Can't seem to get it done right, what am I doing wrong?
let
Source = Excel.CurrentWorkbook(){[Name="Stocks"]}[Content],
#"Invoked Custom…
Patrick Bender
- 407
- 4
- 16
1
vote
1 answer
How to replace text with null in powerquery?
I would like to replace "ValueToReplace" with null in powerquery. This does not work (returns error):
Table.ReplaceValue(#"Previous Step","ValueToReplace", null ,Replacer.ReplaceText,{"Col1"})
This works but it returns "" instead of…
Przemyslaw Remin
- 6,276
- 25
- 113
- 191
1
vote
1 answer
Promote headers inside column containing tables
Suppose we have column Content which contains tables. I would like to transform each table of that column in such a way that all table get headers from first row.
let
Source =…
Przemyslaw Remin
- 6,276
- 25
- 113
- 191
1
vote
1 answer
PowerQuery order of words in Text.Combine
Using PowerQuery in Excel 2016 to combine words in one column by the Category stored in another column. I use GroupBy with Text.Combine in it.
I am expecting the order of words to be sustained but it seems random.
There are 3 pictures below
first…
Lana B
- 496
- 6
- 17
1
vote
3 answers
Powerquery IN operator in conditional column
How this m Power BI M code can be modified
= Table.AddColumn(#"PreviousStep", "Tag", each
if Text.Contains([Column1]) = "value1" then "bingo"
else if Text.Contains([Column1]) = "value2" then "bingo"
else if Text.Contains([Column1]) =…
Przemyslaw Remin
- 6,276
- 25
- 113
- 191
1
vote
1 answer
Excel Powerquery group by text
I'd like to be able to convert text over multiple rows with a common field to a single field over multiple columns.
from this:
to this:
Any help greatly appreciated!!
Chris
- 11
- 1
1
vote
1 answer
M power query - rebuild this data combination
Getting the error message on the below query in M, I've tried splitting into 2 different queries before the merge step but still getting the 'this does not directly access a data source, please rebuild the data combination' error
*let
Source =…
Madalo Furaha Meredith
- 93
- 1
- 4
1
vote
0 answers
DAX way to return summarised data
I hope I'm not missing an easy solution am still getting used to DAX and can't yet find an appropriate logic.
I have a large dataset, >10m rows which I want to test. An identifier column "DocumentNumber" might occur on multiple rows and I want to…
Hugh Barry
- 11
- 2
1
vote
2 answers
How to find & replace in all columns with Power BI
In PowerBI when i use find/replace it produces the following:
= Table.ReplaceValue(Custom2,"-999","",Replacer.ReplaceText,{"Column1"})
But I'd like to replace -999 in all columns, not just Column1.
I'd also like to not have to define a list of all…
Jess
- 19
- 2
1
vote
1 answer
Append all queries in a group as a New Query
I have placed all the queries that I want to append as a new query in one group. I might add a few more in the future. So is there a way we can edit the code to include all the queries in the group
Gangula
- 5,193
- 4
- 30
- 59
1
vote
1 answer
How to retrieve Tag list when extracting user stories/features details via Rally REST API
I use Power Query to call the Rally REST APIs and retrieve data from Rally directly into Excel. I prefer it over the CA AGile Central ADd-In as it allows transformation of the data "on the way".
The problem is that when I try to include the Tags…
Omar
- 7,835
- 14
- 62
- 108
1
vote
0 answers
Handle when Folder.Files returns folders that do not exist
I want Power Query to search the entire C: drive to find all files of a particular extension, such as *.pdf. When I try to filter the file extension in the second step I'm getting this error:
DataSource.NotFound: File or Folder: We couldn't find…
Ben
- 1,168
- 13
- 45
1
vote
1 answer
Quarterly sum of 3 month rolling average in Power BI (DAX or Power Query)
I have monthly results that I need to present as a 3 month rolling average, sometimes at a monthly level and sometimes as a sum of the 3 month rollling average for the quarter/year.
At the monthly level I've found the below formula to work well: …
L118
- 21
- 1
- 4