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 o...
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"
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