How to merge cell contents from one row into another cell/row ?

[cher90]

Hello, 

How to merge cell contents from one row/cell into another cell/row if they have the same ID

I have a table like this:

I am looking to merge the contents of "Data 6" line 7 into "Data 6" line 6 if in "ID" there is the same value. And then duplicate in the other cell.

the expected output:

I then intend to delete the duplicates to keep only one line.

Thanks!

[v-stephen-msft · Accepted solution]

Hi @cher90 ,

The whole M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZAxDoQgEEWvMplaEh3FPYSFByAUCnTbrQ23358xJARD5UuGvOa/Aud4UnhgGUXMNJraBb5veM7vQTjYlX4XYUEHYD84lptmVnzuJLCgE2hivmlmxRf4tj8TWFAI4WUhxqiFRalGtvK1+xGWcs4asEozKv7pBlZKKbH3fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Data 1" = _t, #"Data 2" = _t, #"Data 3" = _t, #"Data 5" = _t, #"Data 6" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Data 1", type date}, {"Data 2", type date}, {"Data 3", type text}, {"Data 5", type text}, {"Data 6", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Tables", each _, type table [ID=nullable number, Data 1=nullable date, Data 2=nullable date, Data 3=nullable text, Data 5=nullable text, Data 6=nullable text]}, {"Combination", each Text.Combine([Data 6], "; "), type nullable text}}),
    #"Expanded Tables" = Table.ExpandTableColumn(#"Grouped Rows", "Tables", {"Data 1", "Data 2", "Data 3", "Data 5"}, {"Data 1", "Data 2", "Data 3", "Data 5"})
in
    #"Expanded Tables"

Steps:

1.Select ID column and then group by.

2.Edit the M code, we replace "List.Sum([Data 6])" with "each Text.Combine([Data 6], " "),"

3.Expand the tables.

Reference: Power Query - Combine rows into a single cell - Excel Off The Grid

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cher90]

Thank you it works very well!

[wdx223_Daniel]

NewStep=Table.Combine(Table.Group(PreviousStepName,"ID",{"n",each Table.ReplaceValue(_,(x)=>Text.Combine([Data 6],","),"",(x,y,z)=>y,{"Data 6"})})[n])