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
Merge Query Matching on Dates in Multiple Rows
I'm trying to merge 2 queries in Power BI Desktop, matching rows based off a user and date column in one query to a row in the other query, where the user matches and the date in the 2nd query is the closest one before the date in the 1st query.
In…
Daniel Kim
- 13
- 3
1
vote
1 answer
After appending, I get null values in primary table headers
I have a table that I want to use as headers for another table that just has data. I used append as new in PBI, used the headers table as primary and data table as secondary. All the columns from the primary table have null values and the data table…
Raul Singh
- 27
- 6
1
vote
0 answers
How to fix "Running out of memory" issue while running power queries in 32 bit ms office 2013
I have a Excel workbook developed in 64 bit ms excel 2016(windows). it has 44 queries/connection. Out of this 40 of them are connections.(there are 6 main tables users will update their values in that based on this we will do several data…
Sreelesh
- 11
- 1
- 4
1
vote
1 answer
Power Query Table.Group List.Sum(two columns)
Trying to figure out how to perform the sum of two columns inside a Table.Group step.
This one throws:
Expression.Error: We cannot convert Type to List type.
M Code:
= Table.Group(PreviousStep, {"PF"}, {{"ColumnName", each List.Sum({[Column1],…
Ricardo Diaz
- 5,658
- 2
- 19
- 30
1
vote
2 answers
Power query rounding incorrect, does not round up or down correctly
I have two custom columns at the end of the query named "Total" and "GST", the "Total" column sums columns "Storage", "PltHire", "Admin", "InActivity", "OutActivity", "Add Activity", "Freight", "Packaging" and "Other". While the "GST" Column is 10%…
Fred
- 43
- 2
- 8
1
vote
2 answers
Issue Adding Column Only if it Doesn't Exists
I am having troubles adding a column that takes the difference between two columns, because the one column doesn't exist.
Below is the formula I am using
= Table.AddColumn(#"Pivoted Column", "InventoryAvailabletoShip", each [On Hand Inventory] -…
MLS
- 108
- 14
1
vote
2 answers
Power Query (M language) 50 day moving Average
I have a list of products and would like to get a 50 day simple moving average of its volume using Power Query (M).
The table is sorted by product name and date. I add a custom column and applied the code below.
if [date] >= #date(2018,1,29)
then…
LAP
- 53
- 2
- 11
1
vote
1 answer
How to apply CONTAINS clause on hierarchical categories to create scenario analysis in Power BI
Building upon the solution given by @olly (Power BI: How to scenario analysis, where the selector "looks up" the select value from slicer and gets values from that row) & file: https://pwrbi.com/so_55281950-2/
In the sample file a "what if" or…
Anton
- 581
- 1
- 5
- 23
1
vote
1 answer
Complex transformation in Excel Power Query
I have 2 input Tables. Input Table1 is the source data and input Table 2 is a criteria table.
+--------------------------+----------+ +--------------------------+-------+
| TABLE 1 (Source data) | | TABLE 2 (Criterias) …
Ger Cas
- 2,188
- 2
- 18
- 45
1
vote
1 answer
Reshaping table Excel PowerQuery
I have a large table in Excel, which is output of a data-gathering tool, that looks more or less like this:
DateA | ValueA | DateB | ValueB | ... | DateZ |…
Tomek S
- 27
- 5
1
vote
1 answer
Power Query to Filter a SQL view based on an Excel column list
Is there a way to filter a SQL view based on a list of values in an excel table column using Power Query?
I have a SQL view that returns a large set of data (millions of records or properties). Users want to filter that based on an excel table…
stevenjmyu
- 926
- 4
- 16
- 31
1
vote
0 answers
How to run stored procedure (authentication) and then display query results
I'm trying to display my query results in Excel for some visuals etc using PowerBI.
To get the data you first need to authenticate yourself using a stored procedure which issues a session_id and then it will display the query results.
When I run…
power_bi
- 11
- 1
1
vote
0 answers
Random Number in Power Query with query folding
I need to generate a column of random numbers in power query. Each row has to have a new value.
Is there a way to do this without breaking query folding??
Number.Random()
returns the same random value for all rows in the table (until query folding…
OscarLar
- 1,315
- 1
- 5
- 15
1
vote
1 answer
Power Query convert from Date to MMM-YY and QYY format
I was wondering if you could help me to convert the data format per below:
01/01/2018 to Jan 18
09/30/2018 to Q3 18
=Table.AddColumn(#"Removed Columns2", "Custom", each Date.ToText([Report Date],"MMM")&"-"&Date.Year([Report Date],"YY"))
This is…
zofia
- 91
- 1
- 1
- 3
1
vote
1 answer
how to use the tables generated by Python/R in Power BI?
I am trying to use Python/R in Power BI. But I do now know how to use the table generated by Python. I do not even know where it is. Here is an example:
id name score
1 zhao 65
2 qian 98
3 sun 100
4 li 45
5 zhou 98
Let's say…
Feng Chen
- 2,139
- 4
- 33
- 62