Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Concatenate text column rows based on unique column key

Hey,  Is theire a way to Concatenate rows in a text column based on a unique identifier column?  I have created an example of my datainput and the output i want:  
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    In a new query step, insert following

    Table.Group(#"Changed Type", {"UniqueOperation ID", "Outermessage"}, {{"Details", each Text.Combine([Details],","), type nullable text}})

    If you want to see complete working for a sample - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRcgTiRJ0kpVgdZJFknRSdVDSxNDDfyMgIyi/UKdIpQRMrUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"UniqueOperation ID" = _t, Outermessage = _t, Details = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueOperation ID", Int64.Type}, {"Outermessage", type text}, {"Details", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"UniqueOperation ID", "Outermessage"}, {{"Details", each Text.Combine([Details],","), type nullable text}})
in
    #"Grouped Rows"

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

In a new query step, insert following

Table.Group(#"Changed Type", {"UniqueOperation ID", "Outermessage"}, {{"Details", each Text.Combine([Details],","), type nullable text}})

If you want to see complete working for a sample - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRcgTiRJ0kpVgdZJFknRSdVDSxNDDfyMgIyi/UKdIpQRMrUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"UniqueOperation ID" = _t, Outermessage = _t, Details = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueOperation ID", Int64.Type}, {"Outermessage", type text}, {"Details", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"UniqueOperation ID", "Outermessage"}, {{"Details", each Text.Combine([Details],","), type nullable text}})
in
    #"Grouped Rows"