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
ronrsnfld
1 year agoSuper User
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