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
77
votes
6 answers
Difference between PowerPivot, PowerQuery and PowerBI
What is the difference between
powerpivot,
powerquery
and powerbi?
What are the use cases for each tool?
What are the limitations?
What requirements must be met to use the tool?
xorpower
- 17,975
- 51
- 129
- 180
55
votes
4 answers
What's the difference between DAX and Power Query (or M)?
I have been working on Power BI for a while now and I often get confused when I browse through help topics of it. They often refer to the functions and formulas being used as DAX functions or Power Query, but I am unable to tell the difference…
user2724541
49
votes
4 answers
Json to excel using power query
I have some json on a website that i want to convert to excel using the power query option from web. But I ran into a small problem. My json looks like this:
[
{
"id" : 1,
"visitors" : 26,
"some_number" : 1,
…
kemis
- 4,404
- 6
- 27
- 40
46
votes
1 answer
How do I comment in Power Query M?
Is there a way to comment M code / comment out lines or blocks of code?
Chris
- 1,313
- 2
- 13
- 26
39
votes
5 answers
Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')
I need to convert the Excel matrix FIRST in the table LATER:
FIRST:
P1 P2 P3 P4
F1 X
F2 X X
F3 X X
F4 X X
LATER:
F P VALUE
F1 P1 X
F1 P2
F1 P3
F1 P4
F2 P1 X
F2 P2 …
user3095042
- 401
- 1
- 5
- 4
37
votes
2 answers
How can I reference a cell's value in PowerQuery
I'm having multiple PowerQuery queries that I would like to feed the value of a cell in my Excel file. In this particular case, the full path to the sourcefile name.
Is there any way I can get this into PowerQuery?
Peter Albert
- 16,917
- 5
- 64
- 88
33
votes
3 answers
PowerQuery: How can I concatenate grouped values?
If I have the following table (shown in the image below), how can I write a grouped query that would concatenate the grouped results?
For this example, I'd want to group by the LetterColumn and concatenate the NumberColumn
So the desired results…
Giffyguy
- 20,378
- 34
- 97
- 168
27
votes
2 answers
Edit SQL in Power Query
When I import data from Sql Server in Power Query, I can paste a query to be executed against the database. But later when I want to edit this query in Workbook Queries > Edit > Advanced Editor, I get something like this:
let
Source =…
AdamL
- 12,421
- 5
- 50
- 74
24
votes
3 answers
How to automate a power query in VBA?
I have data in sheet 1. Normally I go to power query and do my transformations, then close, and load to an existing sheet 2.
I would like to automate this using VBA, where I can just run my power query automatically and populate the transformation…
JonnyBoy
- 385
- 1
- 2
- 10
21
votes
3 answers
Is there any way to invoke PowerQuery/M outside of Excel or PowerBI?
Our BI team is really growing to like the Power Query ETL tool used within Excel and Power BI. The functional language M/PowerQuery has great utility and it would be nice to be able to utilize outside of the context of PowerBI.
Is there or are there…
dkackman
- 15,179
- 13
- 69
- 123
18
votes
5 answers
How can I perform COALESCE in power query?
I have a table that has values for quarters and I need to add a new column that gives me the last not null value as last quarter. For example
ID | Project | Q1 | Q2 | Q3 | Q4 | Current Quarter Value
1 | bal bal | 23 | 32 | 34 |…
dev_huesca
- 456
- 1
- 3
- 14
15
votes
3 answers
What do "Enable Load" and "Include In Report Refresh" mean/do?
In Power Query, when I right-click on the name of a query (in the left-most pane of Power Query's window) I see selections for "Enable Load" and "Include In Report Refresh."
I cannot find an explanation of what these two selections actually mean…
Marc Pincince
- 4,987
- 6
- 19
- 40
14
votes
1 answer
How publish changes to OData feed from Excel
I've created an IUpdateable ODATA feed using WCF which I can connect to using Excel 2010 Power Query. Excel is able to retrieve data but I cannot find a 'publish' button or anyway to tell Excel to push updates back to the server.
Does Excel / Power…
Kye
- 5,919
- 10
- 49
- 84
13
votes
6 answers
Power Query Transform a Column based on Another Column
I keep thinking this should be easy but the answer is evading me. In Excel Power Query, I would like to transform the value in each row of a column based on another column's value. For example, assume I have Table1 as follows:
Column A | Column…
LoganTheSnowEater
- 555
- 2
- 4
- 17
12
votes
5 answers
Excel UNIQUE Across Columns
Is it possible for the new function UNIQUE to be used across various columns & have the output spill into a single column?
Desired output is UNIQUE values in one single column based on all of the values present in Columns: A, B, & C (duplicates in…
urdearboy
- 14,439
- 5
- 28
- 58