Forum Discussion
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 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
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
6 Replies
The function (which may not have been availble when this question was originally posted) to use is: List.Difference()
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
- Table.AddColumn(Step20, "VP", each if (List.Contains({"ZNLD","ZNLM"},[DOC_TYPE]) andList.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]) andnot(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" //Frotaelse null)
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
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
