Forum Discussion
Power Query To Find Items Not Contained in List
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
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