How do I combine values from two adjacent rows in Power Query?

Question

I have an address list in this format:

|ID|AddrType|Company|FName |LName |Address
| 1|Visiting|ACME   |Willie|Coyote|Sand Rd 10
| 1|Delivery|ACME   |Road  |Runner|Sand Rd 20
| 2|Visiting|Go Nuts|Chip  |Munk  |Tree Rd 3
| 3|Visiting|In Sane|Dale  |Munk  |Tree Rd 5

The Delivery type address row is only present if the delivery address differs from the visiting address. So the number of addresses can differ for each company ID

I want to get a result like this:

|ID|V_Company|V_FName|V_LName|V_Address |D_Company|D_FName|D_LName|D_Address
| 1|ACME     |Willie |Coyote |Sand Rd 10|ACME     |Road   |Runner |Sand Rd 20
| 2|Go Nuts  |Chip   |Munk   |Tree Rd 3 |Go Nuts  |Chip   |Munk   |Tree Rd 3
| 3|In Sane  |Dale   |Munk   |Tree Rd 5 |In Sane  |Dale   |Munk   |Tree Rd 5

I have tried and failed using transpose like described in this link: https://community.powerbi.com/t5/Desktop/Merge-2-rows-in-one-row/m-p/46361#M18144

I have also tried and failed to create custom columns where I get values from the row below if the AddrType value on the row below is Delivery.

= Table.AddColumn(#"Sorted columns", "D_FName", each if [AddrTyp]="Delivery" then [FName]{[Index]+1} else FName)

I used this link as a source of visdom bu with limited success: http://excel-inside.pro/blog/2015/11/05/absolute-and-relative-references-in-power-query/

I am sure there is an easy way to get what I want. I now rely on your experience and knowledge to be able to solve it.

Please help me!

MarcelBeug MarcelBeug · Accepted Answer · 2017-05-14T14:35:33

Create a table with second addresses: sort AddressList on AdressType (ascending, so Delivery will come first), buffer and remove duplicates.

The rest of the code should be obvious:

Query "SecondAdresses":

let
    Source = AddressList,
    Sorted = Table.Buffer(Table.Sort(Source,{{"AddrType", Order.Ascending}})),
    SelectDistinct = Table.Distinct(Sorted, {"ID"}),
    D_Headers = Table.TransformColumnNames(SelectDistinct, each "D_"&_)
in
    D_Headers

Query "Result":

let
    Source = AddressList,
    SelectVisiting = Table.SelectRows(Source, each ([AddrType] = "Visiting")),
    RemovedAddressType = Table.RemoveColumns(SelectVisiting,{"AddrType"}),
    V_Headers = Table.TransformColumnNames(RemovedAddressType, each "V_"&_),
    Merged = Table.NestedJoin(V_Headers,{"V_ID"},SecondAddresses,{"D_ID"},"NewColumn",JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "NewColumn", {"D_Company", "D_FName", "D_LName", "D_Address"}, {"D_Company", "D_FName", "D_LName", "D_Address"}),
    RenamedID = Table.RenameColumns(Expanded,{{"V_ID", "ID"}})
in
    RenamedID

How do I combine values from two adjacent rows in Power Query?

1 Answers