Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
109 | |
108 | |
93 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |