Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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'?
Solved! Go to 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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! | |
#proudtobeasuperuser | |
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
)
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.
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on 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.
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!
User | Count |
---|---|
20 | |
18 | |
17 | |
11 | |
7 |
User | Count |
---|---|
29 | |
28 | |
13 | |
12 | |
12 |