Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
bmk
Helper II
Helper II

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

 

bmk_1-1654890933969.png

 

 

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @bmk ,

 

I think I have a solution in Power Query:

Before:

tomfox_0-1654897263457.png

 

After:

tomfox_1-1654897279491.png

 

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/

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

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
Solution Specialist
Solution Specialist

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
Super User
Super User

Hi @bmk ,

 

I think I have a solution in Power Query:

Before:

tomfox_0-1654897263457.png

 

After:

tomfox_1-1654897279491.png

 

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/

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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_0-1655074601279.png

 

 

 

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.