Search code examples
pivottransformtransposepowerqueryunpivot

How would you transform a table with a variable number of locations for an ID so that locations become columns?

I have a table with columns ID and Location. An ID can have multiple (let's say 5) locations. How do I transform the Source Table into the Target Table?

Source Table
ID, Location
Bldg A,Hallway
Bldg B,Back Hallway
Bldg B,Front Hallway
Bldg C,1st Floor
Bldg C,2nd Floor
Bldg C,3rd Floor

Target Table
ID, Location1, Location2, Location3
Bldg A,Hallway,,
Bldg B,Back Hallway,Front Hallway,
Bldg C,1st Floor,2nd Floor,3rd Floor

I have tried to transpose and pivot/unpivot but I cannot get the results I want.

Solution

  • Assuming Table1 has two columns ID and Location

    Select the table and load into powerquery Data...From Table Range ... [x] table has headers

    Select/Click the ID column right-click Group By... [basic] ... Group By:ID ... New column Name:Data ... Operation:All Rows ... Column:blank

    That will aggregate all the data for each ID into a table in its own column

    Add Column..Custom Column....
    New column name: Custom ... formula: =Table.Column([Data],"Location")

    That will create a list for each ID that just has the locations in it

    Click -><- arrows at top of Custom column, extract values, with comma

    That will combine all the unique values for Location with a , to separate them

    Final code (which you could also paste using Advanced...)

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Data", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Data],"Location")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in #"Extracted Values"

    Above has the benefit of being easier to follow, and able to be done using the interface. If you want to get fancy you can use below ... a single step to aggregate the ID and combine the locations. Paste using Advanced Editor 

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Custom", each Text.Combine(List.Transform([Location], Text.From), ","), type text}})
in #"Grouped Rows"