Forum Discussion

VladMatao's avatar
VladMatao
Frequent Visitor
6 years ago
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?

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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

2 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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