Forum Discussion
Solved
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 ...
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
Hi kulkarni21vinee, like this?
Output
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]),
Ad_h = Table.AddColumn(Source, "h", each Text.Combine({[Aircraft number], Text.From(Byte.From(Text.StartsWith([Maintainance Task], "OPEN-O")))}, "_"), Int64.Type),
AddedIndex = Table.AddIndexColumn(Ad_h, "Index", 0, 1, Int64.Type),
GroupedRows = Table.Group(AddedIndex, "h", {{"Aircraft number", each List.First([Aircraft number]), Int64.Type}, {"Maintainance Task", each Text.Combine([Maintainance Task], if Text.StartsWith([Maintainance Task]{0}?, "OPEN-O") then "#(lf)" else "|") , type text}, {"Index", each List.First([Index]), Int64.Type}}, 1,
(x,y)=> Value.Compare(y, x) ),
SortedRows = Table.Sort(GroupedRows,{{"Index", Order.Ascending}}),
RemovedColumns = Table.RemoveColumns(SortedRows,{"Index", "h"})
in
RemovedColumns