Stack Overflow
Stack Overflow

Questions tagged [powerquery]

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.

3147 questions
74
votes
5 answers

Difference between PowerPivot, PowerQuery and PowerBI

What is the difference between PowerPivot, PowerQuery and PowerBI? How should one decided which tool should be used and when.
xorpower
  • 15,315
  • 48
  • 116
  • 173
46
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
43
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,139
  • 1
  • 10
  • 19
35
votes
3 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
  • 3,516
  • 5
  • 23
  • 38
35
votes
4 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
  • 361
  • 1
  • 4
  • 4
27
votes
1 answer

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
  • 15,882
  • 4
  • 59
  • 83
24
votes
2 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
  • 17,946
  • 30
  • 81
  • 147
20
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
  • 10,453
  • 5
  • 46
  • 68
18
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
  • 325
  • 1
  • 2
  • 9
18
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
  • 14,361
  • 11
  • 63
  • 118
13
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,268
  • 9
  • 42
  • 79
12
votes
1 answer

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
  • 3,688
  • 5
  • 14
  • 35
11
votes
1 answer

Excel: require Power Query add-in load from VBA macro

The scenario is a Windows Server 2012 R2, 64 bit; Excel 2010, 32 bit. Many users, just a couple of them have administrative privileges. I installed Power Query from the built-in administrator. Without asking anything, the add-in got installed for…
Enrico
  • 295
  • 1
  • 12
10
votes
4 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
  • 376
  • 1
  • 2
  • 12
9
votes
1 answer

Replace all error values of all columns after importing datas (while keeping the rows)

An Excel table as data source may contain error values (#NA, #DIV/0), which could disturbe later some steps during the transformation process in Power Query. Depending of the following steps, we may get no output but an error. So how to handle this…
visu-l
  • 404
  • 1
  • 4
  • 14
1
2 3
…
99 100