March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Is it possible to convert the table from the left to the table on the right with PowerQuery?
Dont´t mind the labels except Table1, because the already exist in the destination.
In real life Table1 would be an Excel sheet and I have to flatten many of them (Table1 to Table50), each one into a single row:
Thanks for your help!
Solved! Go to Solution.
Not exactly, because you have multi-row column headings, which cannot be done in Power Query or Power BI. But You can get this:
See my table here. I did it in Excel.
Basically, I did this:
If you need this for Excel, this works. I would NOT use this in a Power BI data model. It is not a good model to work with. The DAX will be very difficult. But as an Excel table it will work for a lot of things.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks @edhans , I thought you got it right, it was close enough.
Instead of TableName; Aspect1:Cat1; Aspect2:Cat1; Aspect2:Cat1; Aspect2:Cat2;...
I needed to be: TableName; Aspect1:Cat1; Aspect1:Cat2; ... Aspect2:Cat1; Aspect2;...
To clarify: Aspect1 with all the categories, then Aspect2 with all the categories, and so on.
So I reordered the columns and then it worked, but I give you the credit.
Also, I was able to reproduce almost everything but step 5. I get an error using Table.ColumnNames(). Can you clarify on that?
Thanks for your help.
Yeah, I kinda glossed over that. See this image:
make sense now?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIt makes perfect sense. Thank you, again!
Glad to help!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingNot exactly, because you have multi-row column headings, which cannot be done in Power Query or Power BI. But You can get this:
See my table here. I did it in Excel.
Basically, I did this:
If you need this for Excel, this works. I would NOT use this in a Power BI data model. It is not a good model to work with. The DAX will be very difficult. But as an Excel table it will work for a lot of things.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
13 | |
10 | |
9 | |
7 |
User | Count |
---|---|
39 | |
27 | |
16 | |
15 | |
10 |