Combine text on condition

[kulkarni21vinee]

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 DIFF PRESS LIGHT ON

Expected:

1. Group by Aircraft number
2. Combine Maintainance Task
   1. delimit by | (pipe symbol)
   2. If Maintainance Task starts with word: "OPEN-O", delimit by line feed/line break or #(lf) (~carriage return)

Aircraft number	Maintainance Task
0001	CONTAINER INSPECTION | MSG READ OUT
0001	OPEN-O LOW FUEL QTY TEST
0001	OPEN-O MUST HAVE THEIR EMPLOYEE NUMBER
0002	FEEL DIFF PRESS LIGHT ON

[V-yubandi-msft]

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!

[ronrsnfld · Accepted solution]

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

[dufoq3]

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

[jennratten]

Hello @kulkarni21vinee - 

Below is an example of how you can achieve your desired output. Note, in your description you mention that if Maintenance Task starts with OPEN-O then then delimiter should be a line feed/line break/carriage return.  However, the expected output posted does not actually show the extra line.  Please have a look at this solution and if it does not meet your needs, please provide some additional information so I can revise the solution to something suitable.

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", Int64.Type}, {"Maintainance Task", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Order", each if Text.StartsWith ( [Maintainance Task], "OPEN" ) then 3 else if Text.StartsWith ( [Maintainance Task], "MSG" ) then 2 else 1, Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Aircraft number", Order.Ascending}, {"Order", Order.Ascending}}),
    //   #"Replaced Value" = Table.ReplaceValue(#"Changed Type", each [Maintainance Task], each if Text.StartsWith ( [Maintainance Task], "OPEN-O" ) then [Maintainance Task] & "#(cr)" else [Maintainance Task] & " | ",Replacer.ReplaceValue,{"Maintainance Task"}),

    #"Grouped Rows" = Table.Group(
        #"Sorted Rows", 
        {"Aircraft number", "Maintainance Task"}, 
        {
            {"Text", each Text.Combine ( List.Transform ( [Maintainance Task], (e) => if Text.StartsWith ( e, "MSG READ OUT" ) then " | " & e else if Text.StartsWith ( e, "OPEN-O" ) then e & "#(cr)" else e ) ) , type text }
        }, 0, (x,y) =>
            Number.From ( not Text.StartsWith (x[Maintainance Task], "MSG READ OUT" ) and not Text.StartsWith (y[Maintainance Task], "MSG READ OUT" ) )
            )
in
    #"Grouped Rows"

RESULT: