How to flatten a table into one unique row

[SBPFA]

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!

[SBPFA]

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.

[edhans]

Yeah, I kinda glossed over that. See this image:

1. the Added Custom step uses Table.ColumnNames(Source){0} function.
2. 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"
3. 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?

[SBPFA]

It makes perfect sense. Thank you, again!

[edhans]

Glad to help!

[edhans · Accepted 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:

1. Unpivoted the Aspect1/Aspect2 columns.
2. Merged the Attribute column with Aspect1/2 with the CAT1-5 column.
3. Transposed the table.
4. Promoted it as headers.
5. 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.

[SBPFA]

Thank you @edhans. You're right, this is not for PowerBI, but for Excel.