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.
Yeah, I kinda glossed over that. See this image:
- the Added Custom step uses Table.ColumnNames(Source){0} function.
- The function Table.ColumnNames(Source) returns a list of all column names. It is a Power Query list. The {0} on the end says get the first one. PQ starts numbers at 0, not 1. So the first column in the source data was "Table1"
- The "Source" step is the original unmodified table being pulled into Power Query, so that is the name of the table I used in the Table.ColumnNames() function.
make sense now?
