Pivot Columns

fb7894
Posts: 26
Joined: Thu Aug 16, 2018 11:18 pm

Pivot Columns

Postby fb7894 » Wed May 13, 2020 5:16 am

How can I turn this table:

City...............State
Austin...........TX
Dallas...........TX
Los Angeles....CA
San Fransisco..CA
Houston........TX

Into this table

TX.........CA
Austin....Los Angeles
Dallas....San Fransisco
Houston

alansidman
Posts: 2
Joined: Mon Jun 28, 2021 1:04 pm

Re: Pivot Columns

Postby alansidman » Mon Jun 28, 2021 1:06 pm

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"State", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"State"}, {{"Data", each _, type table [City=nullable text, State=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"City", "Index"}, {"Custom.City", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Data"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[State]), "State", "Custom.City"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom.Index"})
in
    #"Removed Columns1"


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest

cron