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.

4 Replies

  • 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.