Power Query To Find Items Not Contained in List

[TimNYC]

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 equivaluent of SQL's "NOT IN" function. What I need is for this to return all transaction where the paycode is not "BADDEBT" or "WRITEOF"

each if [Payment] = 1 and List.Containts([paycode],"BADDEBT","WRITEOF") then "Paid" else "N/A"

Thank you,

Tim

[jismo]

I created a custom function for this:

let

    fxParam = (x) =>

        let

            source = if x then false else true

        in

            source

in

    fxParam

named it:

fxNot (name is your preference)

then call it like:

fxNot( List.Contains({"APPLES","BANANAS","GRAPES"},"APPLES")) //returns FALSE

fxNot( List.Contains({"APPLES","BANANAS","GRAPES"},"PEARS")) //returns TRUE

Parameter can be any that returns boolean 

[jismo]

actually you can just use the not operator like the below:

not List.Contains({"APPLES","BANANAS","GRAPES"},"APPLES") //returns FALSE

not List.Contains({"APPLES","BANANAS","GRAPES"},"PEARS")) //returns TRUE

[gns100]

The function (which may not have been availble when this question was originally posted) to use is:  List.Difference()

[apo1979prio]

Table.AddColumn(Step20, "VP", each if (List.Contains({"ZNLD","ZNLM"},[DOC_TYPE]) and 

    List.Contains({"500","699","800","999","PT5","PT6","PT8","PT9"},Text.Middle([#".NIF_Venda"], 3, 3)))  then "E"

else if (List.Contains({"ZNLD","ZNLM"},[DOC_TYPE]) and 

    not(List.Contains({"500","699","800","999","PT5","PT6","PT8","PT9"},Text.Middle([#".NIF_Venda"], 3, 3))))  then "P"

else if List.Contains({"ZTOP","ZREP","ZG2P","ZGCP"},[DOC_TYPE]) then "F"  //Frota

else null)

[v-shex-msft · Accepted solution]

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

[az38]

Hi @TimNYC 

use not() like

each if [Payment] = 1 and not(List.Containts([paycode],"BADDEBT","WRITEOF")) then "Paid" else "N/A"

but im not sure it's correct syntax for List.Contains.