How to flatten a table into one single row?

Question

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:

Unpivoted the Aspect1/Aspect2 columns.
Merged the Attribute column with Aspect1/2 with the CAT1-5 column.
Transposed the table.
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.

Unpivot the aspect columns and then transpose maybe? You can't have columns that span other columns like that though. — Alexis Olson, Oct 12 '21 at 21:47
I don't need the aspects to span the categories, labels can be merged. I edited the post to clarify, hopefully — Nicolás Lope de Barrios, Oct 13 '21 at 13:25

score 0 · Accepted Answer · answered Oct 13 '21 at 14:36

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

Unpivot the ASPECT columns
Concatenate ASPECT and CATEGORY as a new column
Remove ASPECT and CATEGORY column
Sort by the concatenated column
Transpose the table
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"

How to flatten a table into one single row?

1 Answers1