Forum Discussion
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 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!
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.
6 Replies
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.
Thanks 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:
- 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?
It makes perfect sense. Thank you, again!
