Forum Discussion

Janica123's avatar
Janica123
Helper I
2 years ago
Solved

Display concatenated distinct values in table

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

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    Janica123
    Helper I
    2 years ago

    Thank you very much Anonymous !

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

     

    Best regards!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.