Forum Discussion

TimNYC's avatar
TimNYC
Helper I
6 years ago
Solved

Power Query To Find Items Not Contained in List

I am aware of the Power Query function List.Contains() to find all items that in the list. But what I need is the opposite of that, to find all items that are not in the list. In other words, the equ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI TimNYC,

    After I test with some sample data, I'd like to suggest you package your wrong code text as a list and compare it with 'List.ContainsAny' function in if statement to achieve your requirement.

    Sample formula:

     

    #"Added Custom"= Table.AddColumn(#"Changed Type", "Custom", each if [Payment] <> 1 then "N/A" else if List.ContainsAny([paycode],{"BADDEBT","WRITEOF"}) then "N/A" else "Paid")

     

    Comment:

    1. 'List.contains' not able to compare with multiple values, please use 'List.containsAny' to instead. If you do not want to use other m query functions, you can write multiple 'list.contains' functions(with each wrong code) and link them with 'and' keyword.

    2. 'List.contains' function will return bool result based on compare results. If you want to extract reverse results, you only need to exchange boolean expressions of your if statement. (exchange true and false expressions)

    Regards,

    Xiaxoin Sheng

Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI TimNYC,

After I test with some sample data, I'd like to suggest you package your wrong code text as a list and compare it with 'List.ContainsAny' function in if statement to achieve your requirement.

Sample formula:

 

#"Added Custom"= Table.AddColumn(#"Changed Type", "Custom", each if [Payment] <> 1 then "N/A" else if List.ContainsAny([paycode],{"BADDEBT","WRITEOF"}) then "N/A" else "Paid")

 

Comment:

1. 'List.contains' not able to compare with multiple values, please use 'List.containsAny' to instead. If you do not want to use other m query functions, you can write multiple 'list.contains' functions(with each wrong code) and link them with 'and' keyword.

2. 'List.contains' function will return bool result based on compare results. If you want to extract reverse results, you only need to exchange boolean expressions of your if statement. (exchange true and false expressions)

Regards,

Xiaxoin Sheng