Forum Discussion
List.Contains combined with not
Hi guys,
I am really struggling with an approach that i am trying to apply. For a big file, i have a list of paycodes that are registered for a particular day. Each list is in a separate cell. I am trying to get to the following: list does not contain. So give me a flag when the list of paycodes, does not contain a specific paycode. I was under the impression that List.Contains could be used with the Not operator, but i was wrong.
My current code looks like:
= Table.AddColumn(#"Renamed Columns2", "Check", each if [DATE] < [Start Date inapte] then "not relevant" else if [DATE] = [Start Date inapte] and not(List.Contains([all],"paycode a")) then "no medical visit" else if [DATE] >= Date.StartOfMonth([Start Date inapte]) and [DATE] <=[#"Start Date inapte - end of month"] and not(List.Contains([all], "paycode b")) then "nu ANPH" else if [DATE] > [#"Start Date inapte - end of month"] and not(List.Contains([all], "paycode c")) then "no AAUH" else "ok")
As mentioned before, i already noticed that the not() part does nothing. I am already aware of the approach in which i put every code that cannot be in the list, into a separate list and then use the List.ContainsAny function, but that does not fully solve it and seems rather inefficient. it does not fully solve it, because the list of unallowed paycodes could grow over time.
Is there any way that i can flag if the specified paycode is not in the list?
Thanks in advance!
Papermain , you might want to try List.Difference,
https://docs.microsoft.com/en-us/powerquery-m/list-difference
2 Replies
- CNENFRNLCommunity Champion
Papermain , you might want to try List.Difference,
https://docs.microsoft.com/en-us/powerquery-m/list-difference
- PapermainFrequent Visitor
Thank you, this works when i do it like this:
= List.Contains(List.Difference(ListIncorrect, ListMain), "paycode a")