Merge rows that are wrongly formated

[VladMatao]

Hello,

As you can see from the picture I have some values in column 3 which should be in one row, instead they span over multiple. That transfer for instance. Is there a way to merge them?

[v-kelly-msft · Accepted solution]

Hi @VladMatao ,

First create a new table which only contains column 3.

Then go to edit queries>"Transform">"Transpose":

"Add column">"Custom Column":

Merge all the columns and use"&"to connect with them:

Finally you will see:

Citys which was put in multiple rows has been merged in one row.

Best Regards,
Kelly

[HotChilli]

I can give you a start (and it will work for your example) but you may need to alter this on a bigger dataset because it groups on the amount which isn't really a good strategy

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBfb4IwFMW/yg1vy5RQcE58Q+icSS1LwbjF+NDpVUmQmtK5+e3lTyRm2e5Te349997T1cqaB2mQAHEc17V6Vp94vu0P/LsCa91bWVzpo8ybF6NGmKvPLMc3eak1z200xiCUBnVW7CE4lTVxhw0RdB7UQ5xGI7b/9JcBHqr61zUc2X47O9WyKHeoodIfied69nNrWCIc5BlB4wazM27BHBB2Ks/Vdz3iJC9HLAzstDqOK2/b7JVCuBCC8vADQhYvImBpdE+TlC4DEcFkMWPRjE9vjLgdSlJBaXoDLOZRzIESGL7EPaB8ygLetYx1ts8KaZQuQWAVA4sNjoH4xCGkL5JwEr07Duk2qCKUWGyruAdZ1kfzK5bBH9PFEXhW+Zfprvdf6A2s9foK", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column3 = _t, Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Column1"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Column1"}, {{"all", each _, type table [Column3=text, Column1=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([all], "Column3")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

Also, quick tip, post data rather than a picture.