Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
TimNYC
Helper I
Helper I

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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
jismo
New Member

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

 

gns100
New Member

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

apo1979prio
Helper I
Helper I

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"], 33)))  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"], 33))))  then "P"
else if List.Contains({"ZTOP","ZREP","ZG2P","ZGCP"},[DOC_TYPE]) then "F"  //Frota
else null)
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
az38
Community Champion
Community Champion

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.


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.