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
PowerQuery Conditional Max
I'm trying to use Power Query with this table:
My goal is to find the max value in Column 3 "GRPEFFDTE" for each Column 1 & Column 2. If this was normal excel I would use:
=+IF(MAXIFS(C:C,B:B,B5,A:A,A5)=C5,1,0)
To make it like the ISMAX column…
Matt Cottrill
- 152
- 1
- 1
- 15
1
vote
1 answer
What skills to concentrate on as a front-end BI Developer?
I would like to enter a career as a BI Developer but concentrating on the front-end side of BI. My understanding is a back-end BI Developer focuses on integration and data-warehouses, whereas a front-end BI Developer focuses on the reporting sides…
James Jones
- 39
- 2
1
vote
1 answer
TOPN in PowerBI DAX not arranging values in proper order
I have been running into some issues with the TOPN function in DAX in PowerBI.
Below is the original dataset:
regions sales
--------------
a 1191
b 807
c 1774
d 376
e 899
f 1812
g 1648
h …
Ali Parahoo
- 161
- 2
- 11
1
vote
1 answer
Declaring variables in Power Query M Functions
I am trying to create a reusable function in Power Query that will be used by several tables.
This works well (written in blank query):
let
is_emergency = (color as text) =>
if color = "red" or color = "orange" then
"emergency"
…
Karl Anka
- 2,529
- 1
- 19
- 30
1
vote
2 answers
Cannot delete "ghost" table from Power Pivot
I have created many power queries and power pivot tables in my Excel file. After some time, I think the data structure may have gotten corrupted, because this is happening:
In Data Model, I have this Community_Membership table that cannot be…
Lun
- 428
- 4
- 20
1
vote
2 answers
power query - M Language - Convert Columns Into Rows
I have a spreadsheet that contains column Names as the product name, quantity, cost.
I want to convert this to rows of data that contain Product Name, Quantity, Cost.
See image below as to what I want.
What is the best way to handle this in Power…
Michael JDI
- 1,241
- 5
- 18
- 30
1
vote
0 answers
PowerBI / PowerQuery: Date slicer for multiple tables
Fellow PowerBI enthusiasts,
I am working on a reporting tool for our support team within our company. They would like to see the hours worked for each operator on a specific selection of dates. This could be just one day, or the whole week.
There…
Sander Otten
- 11
- 3
1
vote
1 answer
Excel Powerquery split table top / bottom 50 percent
I have an example table in Excel to illustrate my question.
Two columns (first name, last name), 11 rows and a header row.
I would like to make get&transform (powerquery) links to another sheet in the same workbook where I would like to have two…
Jan-Willem
- 75
- 1
- 7
1
vote
1 answer
How can I get a percentage field with power pivot?
This should be a fairly easy question for Power Pivot users since I'm a newbie. I am trying to do the following. After pivoting a table I get a crosstab table like this
rating count of id
A 1
B 2
Grand Total …
edd
- 933
- 2
- 11
- 24
1
vote
1 answer
Power BI - Finding records within time span of another record
I have a Jobs table with a start and end datetime, and I have an Events table with the datetime that the event happened.
I'm trying to discover why some jobs take longer than others, so I'd like to filter the page to a specific job and see which…
Alex Eisenhart
- 75
- 6
1
vote
2 answers
How to rename headers using a loop in Power BI?
I have a situation where I need to rename headers based on a table with the old name (HeadersTranslated[HeaderList]), the new name (HeadersTranslated[HeaderEnglish]) and an indeks number (HeadersTranslated[Indeks]).
I am therefore trying to loop…
JSF
- 15
- 3
1
vote
2 answers
Values hard-coded after power query runs is dislocated when data source updates in Excel
I have a table called "COMPANIES" like this:
Company Type
Lafarge Cement
Google IT
Disney Entertainment
The table is then loaded into Power Query and displayed as table COMPANIES2
In table COMPANIES2 in Excel view, I have added a new…
jubinell
- 49
- 4
1
vote
1 answer
How to catch an error in power query when the issue is with excel
I am importing an excel file that has known errors like #N/A and #REF. Power query will error out until the excel is fixed. Well, I don't want to fix the errors in excel. I just want power query to ignore them. It won't. Even when I click…
Henrietta Martingale
- 771
- 8
- 27
1
vote
1 answer
Combine folder of CSV files with different number of columns in Excel Query Editor
I have a folder of many CSV files in which the first eight columns have the same header, but varying amounts of columns after the first eight. I am attempting to use Queries and Connections in Excel 2016 to complete this.
Here is an example of the…
Grant Meehan
- 23
- 2
- 16
1
vote
0 answers
The mashup resources page reader is not available- Error
The mashup resources page reader is not available- Error
I recently started getting this error after about a month of running a macro which ran perfectly for a month with no errors. The macro updates pivots, power queries, and charts. When the error…
UserX
- 157
- 13