Transform rows into columns

[Anonymous]

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?

[stretcharm]

In Query editor pivot.

see some examples here

http://radacad.com/pivot-and-unpivot-with-power-bi

[Anonymous · Accepted solution]

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]

very nice 

[Ian-IKS]

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]

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"

[Ian-IKS]

That's great it works fine now 

[parry2k]

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