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 Dangar332 . I'm having an issue creating a new column in Power Query - it comes up with two errors, first it asks the 'Table' to be #"Table" to fix a Token Literal expected, secondly there is an expression error with ''CONCATENATEX' .
Is CONCATENATEX the DAX language? I think I need it in M language? I want to do it in Power Query so I can unpivot the columns afterwards.
Cheers!
- adudani
Memorable Member
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 🙂- adudani
If 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
- Dangar332
Resident Rockstar
Hi, Anonymous
Ok realize now that you need it in power query(M language) and i post in DAX that's why you got error.and yeah CONCATENATEX is DAX language
and sorry for Misunderstanding
Enjoy your day
