Forum Discussion
kulkarni21vinee
1 year agoFrequent Visitor
Combine text on condition
Actual: Aircraft number Maintainance Task 0001 CONTAINER INSPECTION 0001 OPEN-O LOW FUEL QTY TEST 0001 MSG READ OUT 0001 OPEN-O MUST HAVE THEIR EMPLOYEE NUMBER 0002 FEEL ...
- 1 year ago
And another approach. Also interpreting your "line break" criteria to mean you want the items in the same cell separated by a line break, which is different than what you show in your example:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY5BDsIgFESvMmFdE/QGtR0KCfARPpqm6f2v0brQuHA97+XNthlr7dUMZpKsY8isCLkVThokm3347lKYL4IoL7jOiIeuUDb9ZVJbUDnOkK5/3NSbwo9PQj1DBVOJspLIPd3P8Ee5nYrj2ZiDcyiVrSGGxSvel/YD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Aircraft number" = _t, #"Maintainance Task" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Aircraft number", type text}, {"Maintainance Task", type text}}), #"Add isOPEN-O" = Table.AddColumn(#"Changed Type","is Open-O", each Text.StartsWith([Maintainance Task],"OPEN-O"), type logical), #"Sorted Rows" = Table.Sort(#"Add isOPEN-O",{{"Aircraft number", Order.Ascending}, {"Maintainance Task", Order.Ascending}}), //Note GroupKind.Local #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Aircraft number", "is Open-O"}, { {"Maintainance Task", each if [#"is Open-O"]{0} then Text.Combine([Maintainance Task],"#(lf)") else Text.Combine([Maintainance Task],"|"),type text} },GroupKind.Local), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"is Open-O"}) in #"Removed Columns"Result
If you really want the OPEN-O's in separate cells/rows, then you can use this code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY5BDsIgFESvMmFdE/QGtR0KCfARPpqm6f2v0brQuHA97+XNthlr7dUMZpKsY8isCLkVThokm3347lKYL4IoL7jOiIeuUDb9ZVJbUDnOkK5/3NSbwo9PQj1DBVOJspLIPd3P8Ee5nYrj2ZiDcyiVrSGGxSvel/YD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Aircraft number" = _t, #"Maintainance Task" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Aircraft number", type text}, {"Maintainance Task", type text}}), #"Add isOPEN-O" = Table.AddColumn(#"Changed Type","is Open-O", each Text.StartsWith([Maintainance Task],"OPEN-O"), type logical), #"Sorted Rows" = Table.Sort(#"Add isOPEN-O",{{"Aircraft number", Order.Ascending}, {"Maintainance Task", Order.Ascending}}), //Note GroupKind.Local #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Aircraft number", "is Open-O"}, { {"Maintainance Task", each if [#"is Open-O"]{0} then [Maintainance Task] else {Text.Combine([Maintainance Task],"|")}, type {text}} },GroupKind.Local), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"is Open-O"}), #"Expanded Maintainance Task" = Table.ExpandListColumn(#"Removed Columns", "Maintainance Task") in #"Expanded Maintainance Task"Results2
V-yubandi-msft
1 year agoCommunity Support
Thanks for reaching out to the Microsoft Fabric Community Forum.
Regarding your query about the Combine text on condition. We just wanted to confirm that the solution provided by ronrsnfld is correct and should help resolve the issue you're facing.
If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily. If you continue to face issues, feel free to reach out to us for further assistance!