Forum Discussion

kulkarni21vinee's avatar
kulkarni21vinee
Frequent Visitor
1 year ago
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 ...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    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

     

     

jennratten's avatar
jennratten
Icon for Super User rankSuper User
1 year ago

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: