Forum Discussion
Solved
Merge rows that are wrongly formated
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? ...
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
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.