Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Transform rows into columns

Hi,

 

I'm hoping someone can help me get addresses spread over a few rows into one row. The first table below is my input table and the second table is what I'd like to have as the output. 

 

 

Any thoughts?

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Thanks everyone. I figured it out myself. 

     

    let
    Source = vendors,
    Partition = Table.Group(Source, {"Account No"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Supplier Address", "Index"}, {"Partition.Supplier Address", "Partition.Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Partition.Index", type text}}, "en-AU"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Partition.Index", type text}}, "en-AU")[Partition.Index]), "Partition.Index", "Partition.Supplier Address")
in
    #"Pivoted Column"

    Cheers

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    not sure but try this, select first column, right click and choose "pivot others"

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks everyone. I figured it out myself. 

       

      let
    Source = vendors,
    Partition = Table.Group(Source, {"Account No"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Supplier Address", "Index"}, {"Partition.Supplier Address", "Partition.Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Partition.Index", type text}}, "en-AU"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Partition.Index", type text}}, "en-AU")[Partition.Index]), "Partition.Index", "Partition.Supplier Address")
in
    #"Pivoted Column"

      Cheers