Forum Discussion
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:
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"
2 Replies
- Vijay_A_Verma
Most Valuable Professional
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" if you like text items on distinct lines
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRyi0uKwZSiUnJSrE6aIIpqWmYghmZ6WBBIyMjIC89Pz89JxXISMssKi5RyMnMS8UmW5yanJ+XglM6JxGuNxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, msg = _t, detail = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"msg", type text}, {"detail", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"id", "msg"}, {{"all det", each Text.Combine([detail], ", #(cr)")}}) in #"Grouped Rows"