Forum Discussion

HALEYSTODDARD's avatar
HALEYSTODDARD
Frequent Visitor
4 years ago
Solved

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

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"

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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"

3 Replies

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Impactful Individual
    4 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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"
  • artpil's avatar
    artpil
    Resolver II
    4 years ago

    Hi,

    I created very similar solution to Anonymous  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