Combine text from multiple rows in the same column and filter duplicates

[Kizz]

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

[Kizz]

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!

[Dangar332]

Hi, @Kizz 

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

[adudani · Accepted solution]

hi @Kizz ,

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"

[Kizz]

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

[Kizz]

Thank you! I got it working!

[Dangar332]

hi, @Kizz 

try below measure

Measure = 
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],","
)

[dinesharivalaga]

@Kizz  @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