Forum Discussion
Combine text from multiple rows in the same column and filter duplicates
hi Anonymous ,
create a blank query and copy paste the code below into the advanced editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lHyS8xNVYrViVYyBHKc8pMUgnMzSzLwiHgl5qUquORDNBmBlGTm5CCpAQl55acqeOVn5BXn52EXiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Name", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Data", each _, type table [ID=nullable number, Name=nullable text]}, {"Name List", each Text.Combine(List.Distinct([Name]), ", "), type nullable number}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Name"}, {"Name"}) in #"Expanded Data"
Thank you adudani ! I managed to apply the code to an existing Table (which has the names I want to combine) using the advanced editor but I lost all the other columns (the data set is a bit more complex than my example). The code I kept was:
#"Grouped Rows" = Table.Group(#"Added Custom5", {"ID"}, {{"Data", each _, type table [ID=nullable number, Name=nullable text]}, {"Name list", each Text.Combine(List.Distinct([Name]), ", "), type nullable number}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Name"}, {"Name"})
in
#"Expanded Data"
I'm not sure how this code is removing all the other columns of data. Could you assist? Cheers 🙂
Memorable MemberIf there are no errors until the grouped rows steps, then.
In the expand all data step, select the columns you want to expand.
Let me know if this works for you
Thank you! I got it working!