Combining Multiple Rows

[Anonymous]

Hello, 

I current have some data that appears like this. 

ID  First Name  Surname  Location

3     John            Smith      Engalnd

3     John            Smith      Spain

In some instances a person can have 2 locations. and so is creating 2 rows for the same person. Ideally I would like to transform the data so it adds a new collum, and removes the other row.

ID  First Name  Surname  Location1  Location2

3    John            Smith       England     Spain

I dont know if this is a easy fix, or is something more advanced, but I can't seem to figure it out. 

Any help is appreciated

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Partition = Table.Group(Source, {"ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"First Name", "Surname", "Location", "Index"}, {"First Name", "Surname", "Location", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each "Location "&Number.ToText([Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Location")
in
    #"Pivoted Column"

Hope this helps.

[ryan_mayu]

@Anonymous 

in the powerquery, click group by, set up refer to below screenshot.

update the formula by changing List.Sum([location]) to Text.Combine([location],",")

[amitchandak]

@Anonymous , See if pivoting can help

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/