Forum Discussion
Solved
How to flatten a table into one unique row
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 ...
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:
- Unpivoted the Aspect1/Aspect2 columns.
- Merged the Attribute column with Aspect1/2 with the CAT1-5 column.
- Transposed the table.
- Promoted it as headers.
- Then got the Table1 name using Table.ColumnNames() function and added that as a column, them moved it to the first column.
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.
