Display concatenated distinct values in table

[Janica123]

Hi Community, 

I have a table instead of having a list of all Study_ID's, I'd like to have them in one row

Is that possible in Power BI?

Kind Regards

[v-junyant-msft]

Hi @Janica123 ,

Here is the sample data:

Put this M function into Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxPVdJRMjE1MVKK1UFwTQ1QuGbmKFwLS2SumYEBKtdQKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type number}}),
    GroupedRows = Table.Group(
        #"Changed Type",
        {"Name"},
        {
            {"All_IDs", each Text.Combine(List.Transform(_[ID], each Text.From(_)), ","), type text}
        }
    )
in
    GroupedRows

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Janica123]

Thank you very much @v-junyant-msft !

Is there also a way to only do it in DAX?

Best regards!

[v-junyant-msft · Accepted solution]

Hi @Janica123 ,

You can try to use this DAX to create a new calculated table:

NewTable = 
SUMMARIZE(
    'Table',
    'Table'[Name],
    "Concatenated IDs",
    CONCATENATEX(
        FILTER(
            'Table',
            'Table'[Name] = EARLIER('Table'[Name])
        ),
        'Table'[ID],
        ","
    )
)

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zenisekd]

Hi, I see two ways here.
A) in power query you right-click on the first column and select unpivot other columns and then you merge the other columns with a ", " separator
B) you use Group by and adjust the function to concatenate. https://www.youtube.com/watch?v=eH3ezMimLY0&ab_channel=TechTARSolutions


Kudos and mark as solution appreciated.