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.    ...
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

  • 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

    • parry2k's avatar
      parry2k
      Super User
      8 years ago

      very nice 

      • Ian-IKS's avatar
        Ian-IKS
        Regular Visitor
        8 years ago

        Hi I tried this on my data which is similar but it puts all of the address information against the first row and creates over 8,000 columns. Any tips on what might be wrong. I copied your code