Forum Discussion

bmk's avatar
bmk
Icon for Helper II rankHelper II
4 years ago
Solved

Concatenate or Combine rows based on column condition

Scenario is shown in the below image.

Is there a way to build a measure to concatenate values of column 'Type' based on condition i.e. distinct 'Item Number'?

 

 

 

combine
concatenate
CONCATENATEX
DAX
Distinct
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    Hi bmk ,

     

    I think I have a solution in Power Query:

    Before:

     

    After:

     

    Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg1W0lEyMjYxNQPSZvqGBvpGBkZGCmZWxgZAgZDKglRDEJ2RWawARIkKJanFJUqxOsRoNCJXozG5Gk3I1WiKRWMsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, #"Item Nunber" = _t, #"Date of ocurrence" = _t, Type = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Item Nunber", Int64.Type}, {"Date of ocurrence", type datetime}, {"Type", type text}, {"Description", type text}}),
    #"Goup By" = Table.Group(#"Changed Type", {"Location", "Item Nunber", "Date of ocurrence", "Description"}, {{"Type", each Text.Combine(List.Transform(_[Type], (x) => Value.FromText(x)), ", "), type text}})

in
    #"Goup By"

     

    Let me know if this helps 🙂

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

     

5 Replies

  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Hi bmk ,

     

    I think I have a solution in Power Query:

    Before:

     

    After:

     

    Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg1W0lEyMjYxNQPSZvqGBvpGBkZGCmZWxgZAgZDKglRDEJ2RWawARIkKJanFJUqxOsRoNCJXozG5Gk3I1WiKRWMsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, #"Item Nunber" = _t, #"Date of ocurrence" = _t, Type = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Item Nunber", Int64.Type}, {"Date of ocurrence", type datetime}, {"Type", type text}, {"Description", type text}}),
    #"Goup By" = Table.Group(#"Changed Type", {"Location", "Item Nunber", "Date of ocurrence", "Description"}, {{"Type", each Text.Combine(List.Transform(_[Type], (x) => Value.FromText(x)), ", "), type text}})

in
    #"Goup By"

     

    Let me know if this helps 🙂

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

     

    • bmk's avatar
      bmk
      Icon for Helper II rankHelper II
      4 years ago

      Hello tomfox,

       

      Thank you. This technically worked well. Except for scenarios wherein the 'type' column have same values.
      In which case I would need the distinct values to be concatenated, since currently it is being duplicated.



       

       

       

      • bmk's avatar
        bmk
        Icon for Helper II rankHelper II
        4 years ago

        I just wrapped the list.transform function with list.distinct and it workas required. Please let me know if I need to keep anything in mind from an error handling perspective. Thank you!

  • vapid128's avatar
    vapid128
    Icon for Solution Specialist rankSolution Specialist
    4 years ago

    If it is many to one relationship,

     

    ITEMS = ConcatenateX(RELATEDTABLE(order_goods),order_goods[item],UNICHAR(10),order_goods[item])

    UNICHAR(10) means start new line.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi bmk 

    I guess you are slicing by Location and Item Number. Then you can use

    Types =
CONCATENATEX (
    VALUES ( TableName[Type] ),
    TableName[Type],
    UNICHAR ( 10 ),
    TableName[Type], ASC
)