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 ...
  • ronrsnfld's avatar
    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