Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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 ReportingCheck out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
71 | |
66 | |
24 | |
18 | |
13 |