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 they 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:

I tried 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.

I got Aspect1:Cat1; Aspect2:Cat1; Aspect2:Cat1; Aspect2:Cat2;...

instead of Aspect1:Cat1; Aspect1:Cat2; ... Aspect2:Cat1; Aspect2;Cat2...

To clarify, I need: Aspect1 merged with all the categories, then Aspect2 with all the categories, and so on.

CodePudding user response：

It looks like you've got it from my suggestion if you just add a sorting step (see 4 below).

1. Unpivot the ASPECT columns
2. Concatenate ASPECT and CATEGORY as a new column
3. Remove ASPECT and CATEGORY column
4. Sort by the concatenated column
5. Transpose the table
6. Promote headers

Full M query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnYMMVTSUTKxVAWSxuaqSrE6YEEjINfUECRoZAgXNAZxzUCChghBE5B2Y5AgQrcpkGdpChIzAwnGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CATEGORY = _t, ASPECT1 = _t, ASPECT2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CATEGORY", type text}, {"ASPECT1", Percentage.Type}, {"ASPECT2", Percentage.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"CATEGORY"}, "ASPECT", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "ASPECT_CAT", each [ASPECT] & "; " & [CATEGORY], type text),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ASPECT_CAT", "Value"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"ASPECT_CAT", Order.Ascending}}),
    #"Transposed Table" = Table.Transpose(#"Sorted Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ASPECT1; CAT1", Percentage.Type}, {"ASPECT1; CAT2", Percentage.Type}, {"ASPECT1; CAT3", Percentage.Type}, {"ASPECT1; CAT4", Percentage.Type}, {"ASPECT1; CAT5", Percentage.Type}, {"ASPECT2; CAT1", Percentage.Type}, {"ASPECT2; CAT2", Percentage.Type}, {"ASPECT2; CAT3", Percentage.Type}, {"ASPECT2; CAT4", Percentage.Type}, {"ASPECT2; CAT5", Percentage.Type}})
in
    #"Changed Type1"