You can check multiple values in any list using IN operator in Power Query.
Lets say, you have a requirement to check for specific categories from the list of multiple categories in your condition.
So, in this case you can use Power Query List functions that is List.Contains this function allows you to
check whether the list contains the specified values or not.
Syntax
List.Contains(list as list, value as any, optional as any)
It returns true if value is found in the list otherwise false.
Lets take an example, as you can see here we have a sample table named tblCategory.
Lets, Add one custom column in table that will check for specific categories and if these categories exist then it will show “Yes” otherwise “No”.
Lets go to Power Query Editor, Click on Add Column then click on Custom Column.
Now you will see a Custom Column pop up window opens.
Now we will write a M query formula that will check if the lists contains the category “Fashion”, “Deals”, “Watches” and “Electronics” then show “Yes” otherwise show “No”.
=if List.Contains({"Fashion", "Deals", "Watches", "Electronics"}, [Category]) then "Yes" else "No"
After that click on OK button, and you can see a new column named IsExist is added into table.
You can see for categories “Fashion”, “Deals”, “Watches” and “Electronics” the IsExist column showing status as “Yes” and “No” for remaining categories.
Also Read..
1,145 total views, 1 views today