Concatenate or Combine rows based on column condition

[bmk]

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

[tamerj1]

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
)

[vapid128]

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.

[tackytechtom · Accepted solution]

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]

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]

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!