Power Query - customer column if statement with list.contains and wildcard?

[HALEYSTODDARD]

Hello All,

I'm still fairly new to Power BI, but I'm essentially trying to create a customer column to filter out customer's comments and flag them as a tracking issue. 

so for example, out of this list (pictured below) of customer comments the highlighted comment would be flagged as "transporation issue".

I've read some posts about not being able to use wildcards so I'm wondering if there is another workaround?

I came up with this but I know it's being taken literal and the comments are a free for all so anything goes. 

if List.ContainsAny({[Header Notes]}, {"Fedex", "fedex", "FEDEX", "tracking", "track", "not delivered", "no delivery"}) then "transportation issue" else "valid issue"

[artpil]

Hi,

I created very similar solution to @Vera_33  but I think more customizable.

First I created query with a table with two columns: issue type and list of terms to match issue type. Table name is Issues

let
    Source = Table.FromRecords({[Issue="transportation", Terms={"FEDEX", "TACK","TRACKING", "NOT DELIVERED", "NO DELIVERY"}],[Issue="other", Terms={"FEDEX", "TACK","TRACKING", "NOT DELIVERED", "NO DELIVERY"}]})
in
    Source

then I added column if text matches transportation problems. Column ListOfIssues contains list of matching issue types from table Issues.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVJLbxoxEP4rI3pppGjFbgpEvVWBqpFSFUGlHAhSjW2yLl7Pyg8e/74zdkJROfSwu9b68/car1aD3moRNHjd4V7D1mMHy+9LEE7B9GF5CwfQ/Hj6hBZ9BK0AHeB2C0byuRBR7gC9Ikxv4T9sheyK64pqsL5dDaZGgcNIO1Ib4jNRd2BNiHRuoy0eKniMcBDhHaHAOAinwLiIIC0GjSkWygrmJavSKslY3Bm31y6iP2XBxWz+9OVh9mMxnS0+QzO5b+7qyThv1UW9MyEY95oT1aCP0QvWjK2GDR4z8ptQIKAXcideNe2IyGkPgR1tWN2SU09e90bAV61mR9iQR6pGCpfzWpQiami5KD5NyhUwLZIOtexRGUpQQdb72ZoApuutcKUMpjBO2qRKIcQqpMTk4ksaDptJOFcdWtN3lP9czeXgMHmYPnIAt4MTpizmkrV5MX/Ldx7k0ZaGPtb3d3UzhhsazoUp5mU3VAKFqEefsnjPXSoTJDu8skHAqnpL+e9doD0CNlkzVBligdYfhsNxM6LXqFletMHTKTXTLywD4xGxfrCmP0sL9TuF+Pde5Oq8Ipw9VfBMUxHk6ddgvf4D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Header Notes" = _t]),
    Separators = let ls=List.Range({31..47},1) & List.Range({57..64},1) & List.Range({90..96},1)& List.Range({123..126},1),
binaryData = #binary(ls),
    textFormat = BinaryFormat.Text(
        List.Count(ls),
        TextEncoding.Ascii
    ),
textToSplit=    textFormat(binaryData)
in textToSplit,
    Transportation = Table.AddColumn(Source, "Transportation", each List.NonNullCount( List.Intersect({Text.Split(Text.Upper([Header Notes])," "),Issues[Terms]{0}}))),
    LstOfIssues = Table.AddColumn(Transportation, "ListOfIssues", each List.Accumulate(Table.ToRecords(Issues),{},(cumulator,IssuesRec)=> 
if List.NonNullCount( List.Distinct( List.Intersect({IssuesRec[Terms],List.RemoveNulls(List.RemoveMatchingItems(Text.SplitAny(Text.Upper([Header Notes]),Separators),{"","NULL"}))})))>0 then  cumulator & {Record.Field(IssuesRec,"Issue")} else cumulator ))
in
    LstOfIssues

 Hope this helps,

Artur

[Vera_33 · Accepted solution]

@HALEYSTODDARD 

It is literally working, your initial thought. Split your original column to a list, then transform to lower case, so you don't need all kinds of Fedex, compare the searchList and your original column. Paste the M code in Advanced Editor via Blank query, you will see the steps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqqioUHBLTUmtUKgEAoUqIFCK1YGIp+EQLylKTM7GJZ6Zl44sFQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    searchList={"fedex","track","tracking"},
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if List.Intersect({ List.Transform( Text.Split([Column1]," "), Text.Lower), searchList}) ={}then"valid issue" else "transportation issue" )
in
    #"Added Custom"

[KT_Bsmart2gethe]

Hi @HALEYSTODDARD ,

You can add a custom column and refer to the comment column above by ignoring case sensitive. See the formula below:

if Text.Contains([#"Header Notes"], "fedex", Comparer.OrdinalIgnoreCase) or  Text.Contains([#"Header Notes"], "tracking", Comparer.OrdinalIgnoreCase) or  Text.Contains([#"Header Notes"], "not delivered", Comparer.OrdinalIgnoreCase) then "Transportation issue" else "Valid issue"

Regards

KT