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

  • Question

  • 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
    Monday, January 6, 2020 7:05 PM

Answers

  • Probably more what you want (min. number of LocationN columns)

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Location", type text}}),
        GroupedID = Table.Group(ChangedTypes, {"ID"}, {{"GRP_ID", each Table.AddIndexColumn(_,"LocationN",1,1), type table}}),
        TransformedN = Table.TransformColumns(GroupedID,
            {"GRP_ID", each
                Table.TransformColumns(_,{"LocationN",each "Location"&Text.From(_),type text}),
                type table
            }
        ),
        ExpandedLocationss = Table.ExpandTableColumn(TransformedN, "GRP_ID", {"Location", "LocationN"}, {"Location", "LocationN"}),
        PivotedLocationN = Table.Pivot(ExpandedLocationss, List.Distinct(ExpandedLocationss[LocationN]), "LocationN", "Location")
    in
        PivotedLocationN


    Monday, January 6, 2020 10:15 PM
  • Same result as previous reply w/o pivoting

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Location", type text}}),
        MaxColumns = List.Max(List.Transform(GroupedID[LocList], each List.Count(_)))-1,
        GroupedID = Table.Group(ChangedTypes, {"ID"}, {{"LocList", each Text.Split(Text.Combine(_[Location],"|"),"|"),type list}}),
        AddedLocations = List.Accumulate({0..MaxColumns},GroupedID,
            (iTable,N)=>
                Table.AddColumn(iTable,"Location"&Text.From(N+1), each
                    try [LocList]{N} otherwise null, type text
                )
        ),
        RemovedLocList = Table.RemoveColumns(AddedLocations,{"LocList"})
    in
        RemovedLocList

    Monday, January 6, 2020 11:08 PM

All replies

  • Hi Jack

    Assuming data in Excel Table1, one way:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Location", type text}}),
        DistinctLoc = List.Buffer(List.Distinct(ChangedTypes[Location])),
        LocationN = Table.AddColumn(ChangedTypes,"LocationN", each "Location" & Text.From(List.PositionOf(DistinctLoc,[Location])+1), type text),
        PivotedLocationN = Table.Pivot(LocationN, List.Distinct(LocationN[LocationN]), "LocationN", "Location")
    in
        PivotedLocationN

    Monday, January 6, 2020 8:34 PM
  • Probably more what you want (min. number of LocationN columns)

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Location", type text}}),
        GroupedID = Table.Group(ChangedTypes, {"ID"}, {{"GRP_ID", each Table.AddIndexColumn(_,"LocationN",1,1), type table}}),
        TransformedN = Table.TransformColumns(GroupedID,
            {"GRP_ID", each
                Table.TransformColumns(_,{"LocationN",each "Location"&Text.From(_),type text}),
                type table
            }
        ),
        ExpandedLocationss = Table.ExpandTableColumn(TransformedN, "GRP_ID", {"Location", "LocationN"}, {"Location", "LocationN"}),
        PivotedLocationN = Table.Pivot(ExpandedLocationss, List.Distinct(ExpandedLocationss[LocationN]), "LocationN", "Location")
    in
        PivotedLocationN


    Monday, January 6, 2020 10:15 PM
  • Same result as previous reply w/o pivoting

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Location", type text}}),
        MaxColumns = List.Max(List.Transform(GroupedID[LocList], each List.Count(_)))-1,
        GroupedID = Table.Group(ChangedTypes, {"ID"}, {{"LocList", each Text.Split(Text.Combine(_[Location],"|"),"|"),type list}}),
        AddedLocations = List.Accumulate({0..MaxColumns},GroupedID,
            (iTable,N)=>
                Table.AddColumn(iTable,"Location"&Text.From(N+1), each
                    try [LocList]{N} otherwise null, type text
                )
        ),
        RemovedLocList = Table.RemoveColumns(AddedLocations,{"LocList"})
    in
        RemovedLocList

    Monday, January 6, 2020 11:08 PM