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

      • DAX0110's avatar
        DAX0110
        Resolver V
        8 years ago

        The second step should be a Fill Down.... revised code:

         

        let
    Source = vendors,
            FillDown = Table.FillDown(Source, {"Account No"}),
    Partition = Table.Group(FillDown, {"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"