Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 a...
Need Help
  • adudani's avatar
    adudani
    2 years ago

    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"

     

     

Dangar332's avatar
Dangar332
Icon for Resident Rockstar rankResident Rockstar
2 years ago

hi, Anonymous 

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

 

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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