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
6
votes
2 answers
How can I turn the deepest elements of nested JSON payload into individual rows in Power Query?
Goal:
I have a JSON payload with the following format:
{
"Values": [
{
"Details": {
"14342": {
"2016-06-07T00:00:00": {
"Value": 99.62,
…
Craig
- 1,890
- 1
- 26
- 44
6
votes
4 answers
How to change source of a power query using VBA?
So in Excel 2016, they have this neat tool called Power Query, basically a glorified excel table. Every table has steps in it for filtering, removing columns, etc... The first step is the source step, to assign a connection string basically to…
James Heffer
- 686
- 1
- 6
- 17
6
votes
1 answer
Excel Power Query - convert date time from UTC to Local
I'm connecting to an MS SQL database via Power Query (Version: 2.10.3598.81) in Excel 2010.
My dates are stored in UTC in the database, however I would like to show them as local in the spreadsheet.
DateTimeZone.From() - converts to a DateTimeZone…
AdamH
- 1,331
- 7
- 19
5
votes
2 answers
How to edit an already created Python Script in PowerBI?
In my Power BI dashboard, I created a Python Script that accesses an API and generates a Pandas data frame.
It works fine, but how can I edit the Python code?
I thought it would be something simple, but I can't really find how to find it in the…
neves
- 33,186
- 27
- 159
- 192
5
votes
1 answer
How to show data properly in Office Excel Using Power Query Editor?
I have below JSON output from an API, in Office Excel I am importing data via Web from API.
[{
"level": 1,
"children": [{
"level": 2,
"children": [{
"level": 3,
"name":…
Jackson
- 1,426
- 3
- 27
- 60
5
votes
3 answers
Is there a way to add line breaks in a string of text in M (Power BI)?
I concatenated four columns using this code (to prevent null values to be linked together) in Power Query (Power BI Desktop):
= Text.Combine(List.Select(
{ [Col1], [Col2], [Col3], [Col4]
}, each _<> "" and _ <> null),"; "))
I was wondering if…
DatAlessia
- 73
- 1
- 1
- 7
5
votes
2 answers
Excel: How to gather unique values in one column that are associated with duplicates in another column?
With data such as this:
Column A Column B
1 98
1 12
1 21
1 31
2 37
2 40
3 48
4 34
4 88
4 74
4 …
Stian
- 73
- 1
- 6
5
votes
5 answers
How to use DATEDIFF in Power Query (M query) to look for months between two dates
I need to find the month difference between two dates (checkIn and Checkout dates) in Power Query (M-query). It can be similar to DAX bellow.
period of months = DATEDIFF([dateCheckIn], [dateCheckOut], MONTH )
I found the function
daysDiff = each…
Thao N
- 81
- 1
- 2
- 8
5
votes
3 answers
Insert new column with list of values in PowerQuery/M
If I have the following source:
#"My Source" = Table.FromRecords({
[Name="Jared Smith", Age=24],
[Name = "Tom Brady", Age=44],
[Name="Hello Tom", Age = null],
[Name = "asdf", Age = "abc"]
}),
How would I add a…
David542
- 104,438
- 178
- 489
- 842
5
votes
2 answers
Regexp in Power Query using JavaScript
I need Regexp, while M / Power Query doesn't have native support to it. I found several variants of solution around the same excellent Web.Page & JavaScript idea - Biccauntant, Hugoberry.
I had to adopt them (the resulting code is below) due to…
Andrey Minakov
- 545
- 2
- 5
- 19
5
votes
6 answers
Power BI: Convert text (yyyymmdd) to date (dd/mm/yyyy)
I'm specifically trying to convert string in Column A : yyyymmdd to dd/mm/yyyy date format using Power Query Editor in Power BI. I can already perform this in Excel using the formula below:
Any ideas
Excel
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
obskure_ie
- 71
- 1
- 1
- 5
5
votes
1 answer
Store Power Query custom function Online (Github etc..,) and call it
Main Question :
I have created various custom functions that I frequently use. I would like to store them in a repository somewhere and call them whenever necessary. I know that I can save all those functions in an Excel file and save it in…
Gangula
- 5,193
- 4
- 30
- 59
5
votes
1 answer
How to refresh report that uses custom functions on Power BI Service?
I know this issue has been posted many times before, but I was not able to get a solid/working solution.
I am trying to figure out a workaround for refreshing dataset with Custom functions over Power BI service. My query looks like the following:…
Pratik Bhavsar
- 808
- 8
- 32
5
votes
3 answers
VBA Wait for refresh of power query to execute next line of code
I am working on a VBA project, that requires update of a specific table via power query as part of the code.
The code power query refresh needs to finish, before the query continues, but, i have not managed to find a solution to do that yet.
Option…
Tue Herlevsen
- 65
- 1
- 1
- 3
5
votes
3 answers
Add missing date rows in Power BI/Power Query and take value of row above
Say I am importing something like the following into PowerBI:
Date | Quantity
|---------------------|------------------|
| 1/1/2018 | 22 |
| 1/3/2018 | 30 |
| …
Matt C.
- 2,330
- 3
- 22
- 26