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
Excel - Power Query - how to create a new query based on the existing one
I have created a number of power queries. So far I have done them by merging two sources (Merge as new). Now just want to extract from the existing query. I don't need to merge it with another one. I'd like to do some filtering on the query and save…
Wasteland
- 4,889
- 14
- 45
- 91
1
vote
2 answers
M Power Query - Expand Columns Dynamically
I'm trying to expand columns after converting to a table, but these columns need to be expanded dynamically.
I've researched online but the code I'm adding just does not work one way or another. I must be missing something but i am not sure.
This is…
Hulk Smash 93
- 55
- 1
- 8
1
vote
1 answer
Is there a way to force eager evaluation on Power Query M Table and Record expressions?
I'am working on a Monte Carlo visualisation in PowerBI. So far i was able to perform the Monte Carlo simulation, but i'am stuck on creating the visualisation.
I created a table with two columns, iteration and result, which can have any number of…
ABos
- 56
- 1
- 1
- 7
1
vote
1 answer
Power Query - Normalise columns
How do you normalise all columns by their maximum value?
See the following table which we start with:
The result should be that all columns are divided by their own maximum value, as such:
The Power Query script should:
Apply to all columns,…
InteressantPunt
- 145
- 4
1
vote
1 answer
Only refresh new files in Excel Power Query for data from folder?
We receive new data pretty much everyday, which is saved in a local folder. I've created a data model in Power Query from these data to process and create visualization, which whenever refreshed will run through the whole folder again. As you might…
Linh
- 23
- 5
1
vote
0 answers
Excel Table of orders, calculating running total of on hand inventory
The Goal: Calculate the running demand by product as listed by customer desired date to know which orders can ship and at what point (future date) the on-hand inventory will not support open orders.
The Data: A Microsoft query against a Microsoft…
John M - Fanblade
- 11
- 2
1
vote
1 answer
Variable in a Power BI query
I have a SQL query to get the data into Power BI. For example:
select a,b,c,d from table1
where a in ('1111','2222','3333' etc.)
However, the list of variables ('1111','2222','3333' etc.) will change every day so I would like the SQL statement to…
Dyniel
- 11
- 2
1
vote
2 answers
DAX - Histogram for relative frequencies and total column
I built a diagram that displays the relative frequencies of my clusters (on the values in the column) and the cumulated frequencies (on the values on the line).
My chart has this aspect:
I would like to add a new column to the right that is equal…
Lorenzo Benassi
- 621
- 1
- 8
- 31
1
vote
1 answer
How to merge multiple tables in Power BI while also appending a a new column that contains the name of the table ?
I figured out how to take my audit tables and combine them..
Audit Company
Audit Contacts
Audit Referrals etc..
So Now I need an additional column in my merged table so I know what table the data came from ? How can I do this in power query?
punkouter
- 5,170
- 15
- 71
- 116
1
vote
0 answers
SharePoint Tables - Build URL using Project ID
I am trying to connect to a Sharepoint project.
This is the function that I have created.
(ReqURL as text, ReqTitle as text) =>
let
Source = SharePoint.Tables(ReqURL, [ApiVersion = 15]),
ReqData = Source{[Title=ReqTitle]}[Items]
in
…
Student of the Digital World
- 2,971
- 16
- 62
- 116
1
vote
2 answers
How do I properly use table.group in a PowerQuery query to dynamically summarize different rows and columns?
I created a table in Excel 2016 where I put various names of columns, columns I wanted to summarize, names to set new column names to, etc. Essentially what I wanted to be a parameter table.
I then created a function in the Power Query editor and…
user2943131
- 107
- 3
- 10
1
vote
3 answers
SQL - Julien Date (CYYDDD) to date
Unfortunately, this is my first approach with SQL!
I am creating with the following code a query between an oracle DB and Excel (Power Query).
select "$Table"."Order" as "Order",
"$Table"."NR" as "Nr",
"$Table"."JDDATE" as "JDDATE"
from…
user10732287
1
vote
2 answers
Power Query - replacing text in a new column
I am new to Power Query in Excel and my question is:
I have a text column with date in this format "09-Feb-17 A". To remove the text " A" and populate the date info in a new column (custom column?), I have used this code:
=…
B.B
- 35
- 7
1
vote
1 answer
Power BI - handling non-existing arguments and returning data in table for valid arguments
I am stuck at the error handling routine. I have this function..
(LicenceNumber) =>
let
Source =
Web.Page(Web.Contents("http://mbsweblist.fsco.gov.on.ca/ShowLicence.aspx?M"
& Number.ToText(LicenceNumber) & "~")),
WebData =…
suji
- 43
- 8
1
vote
1 answer
LOOKUPVALUE based upon aggregate function in DAX
I need a calculated column (because this will be used in a slicer) that returns the employee's most recent supervisor.
Data sample (table 'Performance'):
EMPLOYEE | DATE | SUPERVISOR
--------------------------------------------
Jim …
C. Rush
- 62
- 2
- 10