topic Re: Concatenate or Combine rows based on column condition in DAX Commands and Tips

Concatenate or Combine rows based on column condition

bmk — Fri, 10 Jun 2022 19:57:25 GMT

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'?

Re: Concatenate or Combine rows based on column condition

tackytechtom — Sat, 10 Feb 2024 14:01:25 GMT

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/

Re: Concatenate or Combine rows based on column condition

vapid128 — Fri, 10 Jun 2022 23:03:10 GMT

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.

Re: Concatenate or Combine rows based on column condition

tamerj1 — Sat, 11 Jun 2022 08:52:03 GMT

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 )

Re: Concatenate or Combine rows based on column condition

bmk — Mon, 13 Jun 2022 03:24:47 GMT

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.

Re: Concatenate or Combine rows based on column condition

bmk — Mon, 13 Jun 2022 03:25:10 GMT

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!