Search code examples
powerquerym

Collect related values from more than one column and row into a single column

I have this table:

enter image description here

Which I'd like to change to this:

enter image description here

As you can see, I want to collect all the related entries from all of the P1 rows, across all columns, into a single column under P1, and do the same for P2 and P3 related entries.

Is there a simple way to do this in PowerQuery / M?

Solution

  • Yes:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"P", type text}, {"15", Int64.Type}, {"25", Int64.Type}, {"35", Int64.Type}, {"45", Int64.Type}, {"55", Int64.Type}, {"65", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"P"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"P"}, {{"AllData", each _, type table}}),
    TablesToLists = Table.TransformColumns(#"Grouped Rows",{{"AllData", each _[Value]}}),
    #"Transposed Table" = Table.Transpose(TablesToLists),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    TableFromColumns = Table.FromColumns(Record.FieldValues(#"Promoted Headers"{0}),Table.ColumnNames(#"Promoted Headers"))
in
    TableFromColumns