Forum Discussion
Combine text from multiple rows in the same column and filter duplicates
Hi all,
I've been trying to find an answer to my problem but alas I haven't found a Power Query (M) answer to this.
I would like to combine the names in a column based off the ID and filter any duplicates.
For example, my data looks like this:
| ID | Name |
| 1 | Bob Smith |
| 1 | Bob Smith |
| 1 | Jane Doe |
| 2 | Bill Smith |
| 2 | Joe Johnson |
| 2 | Joe Johnson |
And I want it to look like this:
| ID | Name | Name list |
| 1 | Bob Smith | Bob Smith, Jane Doe |
| 1 | Bob Smith | Bob Smith, Jane Doe |
| 1 | Jane Doe | Bob Smith, Jane Doe |
| 2 | Bill Smith | Bill Smith, Joe Johnson |
| 2 | Joe Johnson | Bill Smith, Joe Johnson |
| 2 | Joe Johnson | Bill Smith, Joe Johnson |
I'm new to PowerBI so any help would be appreciated!
Cheers,
K
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"
8 Replies
- Dangar332
Resident Rockstar
hi, Anonymous
try below measureMeasure = CONCATENATEX( FILTER( ALL('Table'[ID],'Table'[Name]), 'Table'[ID]=MAX('Table'[ID]) ), 'Table'[Name],"," )or for new column use below code
Column = CONCATENATEX( FILTER( ALL('Table'[ID],'Table'[Name]), 'Table'[ID]=EARLIER('Table'[ID]) ), 'Table'[Name],"," )Anonymous adudani Hey experts ,
I am also facing the same logic and below the content from my side .
In my dataset , I have applied an Unpivot column method for the selected columns , so after that all the rows were duplicated with unpivot data .Now each account names (Row) has 27 duplicated rows post unpivot selected columns applied .
I have already tried to use your approches also but no luck due to the multiple context available in my dataset.
Hence it is showing different errors now with some other approached i did .
DAX : Merged Comments = SUMMARIZE('Test Delivery Updates','
Test Delivery Updates'[Account Name],
"New Comments",
CONCATENATEX('Test Delivery Updates',
'Test Delivery Updates'[Comments] ,"&",'Test Delivery Updates'[Comments],ASC
) )
Error -- "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
Please help to solve this ...
Thanks
DK
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 🙂
- Dangar332
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
