Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

IF and IN and Wildcards

Hello

I am trying to convert some code I had in Qlikview to Powerbi.

It is saying that if the account is in the list of accounts below than flag as Y else as N

It also has a wildcard for 1* account codes and 61* account codes. 

How would you write the below in PowerBI.  I can write for where groupcoa  = 1101 (see below) but not when using a list

Qlikview

if(wildmatch(account,'1*','61*','71*','7981','8985','81*','84*','3012','3016','3019','3106','3108','3450','3455','3605','3601') > 0, 'Y','N') as [Agresso/eFront Only]

PowerBI

= Table.AddColumn(#"Changed Type", "Custom", each if [groupcoa] = 1101 then "N" else "Y", type text)

Need Help
  • jennratten's avatar
    jennratten
    4 years ago

    Hello - please give this a try.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MFSK1YlWMjM0MAAzjA0NzGAMqIiJKYwBEzEzMIUxgNpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [groupcoa = _t]),
    // List of values to compare
    varCriteria = {"1*","61*","71*","7981","8985","81*","84*","3012","3016","3019","3106","3108","3450","3455","3605","3601"},
    // List of wild card criteria
    varWildCardStartsWith = List.Transform ( List.Select ( varCriteria, each Text.Contains ( _, "*" ) ), each Text.Remove ( _ , "*" ) ),
    // List of exact match criteria
    varExactMatch = List.Select ( varCriteria, each not Text.Contains ( _, "*" ) ),
    // Search for matches
    FoundInList = Table.AddColumn( Source, "FoundInList", (FindStrings) => 
        // check for starts-with wild cards using the Text.StartsWith function
        List.AnyTrue(
            List.Transform(
                varWildCardStartsWith, each Text.StartsWith(FindStrings[groupcoa], _, Comparer.OrdinalIgnoreCase )
            )
        // also check for exact matches
        ) or List.Contains ( varExactMatch, FindStrings[groupcoa] ), type logical
    )
in
    FoundInList

      Add a blank query inside the Power Query Editor, then open the Advanced Editor for the script and replace the entire contents of the window with this script.  This include comments added to the script to explain what's going on.

     

     

     

1 Reply

  • jennratten's avatar
    jennratten
    Icon for Super User rankSuper User
    4 years ago

    Hello - please give this a try.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MFSK1YlWMjM0MAAzjA0NzGAMqIiJKYwBEzEzMIUxgNpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [groupcoa = _t]),
    // List of values to compare
    varCriteria = {"1*","61*","71*","7981","8985","81*","84*","3012","3016","3019","3106","3108","3450","3455","3605","3601"},
    // List of wild card criteria
    varWildCardStartsWith = List.Transform ( List.Select ( varCriteria, each Text.Contains ( _, "*" ) ), each Text.Remove ( _ , "*" ) ),
    // List of exact match criteria
    varExactMatch = List.Select ( varCriteria, each not Text.Contains ( _, "*" ) ),
    // Search for matches
    FoundInList = Table.AddColumn( Source, "FoundInList", (FindStrings) => 
        // check for starts-with wild cards using the Text.StartsWith function
        List.AnyTrue(
            List.Transform(
                varWildCardStartsWith, each Text.StartsWith(FindStrings[groupcoa], _, Comparer.OrdinalIgnoreCase )
            )
        // also check for exact matches
        ) or List.Contains ( varExactMatch, FindStrings[groupcoa] ), type logical
    )
in
    FoundInList

      Add a blank query inside the Power Query Editor, then open the Advanced Editor for the script and replace the entire contents of the window with this script.  This include comments added to the script to explain what's going on.