Power Query IF statements come in different forms: Power Query IF OR specifies two conditions to be evaluated (separately) for stating them as true or yielding the desired output. Find centralized, trusted content and collaborate around the technologies you use most. ); If your organization uses Microsoft Azure cloud to store, manage and access information, you can combine your Azure cloud with Power BI using this guide Connect Azure to Power BI: A Comprehensive Guide. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills. Using Power Query IF statements to sort & filter columns. Here it is referring the entire column as a list to check against another list. They are very much like the others as well and you can add in other conditions instead of just checking for membership. The methods above provide examples of the various approaches we could take. https://learn.microsoft.com/en-us/powerquery-m/list-contains, Get data into Power Query 5 common data sources, DataRefresh Power Query in Excel: 4 ways & advanced options, Use the Power Query editor to update queries, Get to know Power Query Close & Load options, Common Power Query transformations (50+ powerful transformations explained), Power Query Append: Quickly combine many queries into 1, Get data from folder in Power Query: combine files quickly, List files in a folder & subfolders with Power Query, How to get data from the Current Workbook with Power Query, How to unpivot in Excel using Power Query (3 ways), Power Query: Lookup value in another table with merge, How to change source data location in Power Query (7 ways), Power Query formulas (how to use them and pitfalls to avoid), How to use Power Query Group By to summarize data, Power Query Common Errors & How to Fix Them, How to create nice looking Office Scripts button (3 ways), How to email or save Excel images with Power Automate, How to create dynamic chart legends in Excel, How to run Power Automate from Excel with Office Scripts or VBA, How to fix the Formula.Firewall error in Power Query (2 ways), Application.CutCopyMode = False (How to use it), How to install or uninstall Excel Add-ins (Windows & Mac), INDEX MATCH MATCH in Excel (How to do 2-dimension lookup), Advanced dynamic array formula techniques (3 methods), How to use dynamic arrays with other features (7 scenarios), RANDARRAY function in Excel (How to + 4 Examples), SEQUENCE function in Excel (How to + 5 Examples), VBA code to copy, move, delete and manage files, Using the Conditional Column feature for basic scenarios, Writing M code for more advanced scenarios. Conditional Column versus Custom Column, 4.3 Expression.SyntaxError: Token Literal expected, 4.4 Expression.SyntaxError: Token Then/Else expected, How to use Lists in Power Query Complete Guide . A cutdown version of the problem is: let TableA = Table.FromColumns ( { {1,2,4}}, {"A"}), ListB = {4,5,6 }, DPart = Table.AddColumn (TableA, "IsInB", List.MatchesAny (ListB, each _ = [A])) in DPart It will tell you that: [powerquery] Read other blogs, or watch YouTube videos on the same topic. Returns true if value is found in the list, false otherwise. Browse other questions tagged, Where developers & technologists share private knowledge with coworkers, Reach developers & technologists worldwide, Power query,Check if value in column A contains one of values in column B, The open-source game engine youve been waiting for: Godot (Ep. For the DAX version of the Power BI IF Statement, we have a separate detailed guide that you can check out here How to Use Power BI IF Statement: 3 Comprehensive Aspects. If you don't expand it and then load a query to a worksheet, you see a placeholder value of [List] for each cell in the column. Are you looking to: Hope that gives you some clues on how to continue. This is to make the code easier to read, but it is not necessary for the code to be valid. Tree (data structure) This unsorted tree has non-unique values and is non-binary, because the number of children varies from one (e.g. Then you can act accordingly to that. Theoretically Correct vs Practical Notation, Find a vector in the null space of a large dense matrix, where elements in the matrix are not directly accessible, Dealing with hard questions during a software developer interview, Can I use a vintage derailleur adapter claw on a modern derailleur. Required fields are marked *. Check if column contains any value from another table's column. For example, the syntax below reverses the result of the logical test. However, this may be new to you if youre coming from a purely Excel world. Example 1 Determine if the table contains the row. Therefore, if we use the formula above, then edit the step, Power Query will open the Conditional Column dialog box rather than the Custom Column dialog box. Power Query can generate any consecutive list as long as it fits within a 32 bit integer value. Power Query in Power BI constructive tool for importing data from a variety of sources. Please see my screenshot that shows the measure as . I am sorry that I cannot participate in the discussion now. I am currently building a flow that sends e-mails listed in an Excel sheet named 'Mails'. These last two errors are a bit clearer, but can still confuse users. Identifying If a column contains a value and returning true/false. Results = No Data To use Power Query if logic, we need a programming-based approach rather than the function-based approach we find in Excel. Returns true if the value is found. Imagine you want to add a column that specifies whether a line refers to a single product unit or multiple product units. As a bonus, these two functions are very helpful as well List.AnyTrue and List.AllTrue. You can use this menu to define and use basic IF statement logic. Rick is the founder of BI Gorilla. There are two ways to create this type of conditional logic in Power Query: I recommend you download the example file for this post. Please note that the conditional column feature supports basic Power Query IF statement logic; the ones which can be fairly expressed as a single sentence in English. cant be performed through the provided menu. This function doesn't support wildcards or regular expressions. I have tried all sorts of modifications and nothing has worked. listeners: [], An IF statement is a logical formula. Or logic performs multiple logical tests, but only requires a single true response to return the true result. It allows you to create basic if-statements. So, I started searching for the secrets to automating Excel. With some basic examples you easily learn how to write conditional if statements in Power BI. = if not something is true then true else false. Returns TRUE both 4 and 5 are on the list. SelectAdd Column > Conditional Column. and allows you to transform your data into the right shape and condition for better analysis. Its a great function to use in Power Query. Below is my current flow. Check whether Value Exists in Column from different Table ('VLOOKUP' within Condition). If you need to perform an exact match, check out my other video: https://youtu.be/KnvSAAqfUX0Chapters:0:00 Intro0:22 Recap: exact match0:56 Method 1: Splitter.SplitTextByAnyDelimiter4:01 The issue with Text.Contains4:50 Method 2: Leverage Text.Contains in a functionABOUT BI Gorilla:BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills. Unable to process template language expressions for action 'Condition_4' at line '1' and column '2485': 'The template language function 'contains' expects its first argument 'collection' to be a dictionary (object), an array or a string. } 2) Add another column Select which selects from each list above only values which meet condition. If you omit the word and replace them by a separator, you would get one of the following error messages: Expression.SyntaxError: Token Then expected. } Powered by Rocket.net, FlyingPress Built on theme GeneratePress, 2. Next, lets revisit Scenario 2. In these types of scenarios, I advise using brackets (or parentheses, as you may call them) to simplify the order of calculation. The shown examples create a new column based on logic. While Power Query is just limited to Excel sheets and CSV file formats, why not import data from Databases like MySQL and PostgreSQL, SaaS applications like Mailchimp, Zendesk, and CRMs like Salesforce, and HubSpot to Power BI? OK, so it popped up in the Power Query forum for Power BI here. In above I didn't care about errors . Type in your new column name under the heading New column name. Want to learn more about lists? Your email address will not be published. To make your conditions a bit more advanced you can use common operators. It allows you to make comparisons between a value and what youre looking for. Indicates whether the list list includes any of the values in another list, values. There are many ways we could address this solution. I have a silly problem tough: I cant get PowerQuery to recognize as a formula the and and or operators. Else Sales Value is greater than 6500, then Output is 300. In the example, additional regions are added including South, Northeast, and Midwest. Power query,Check if value in column A contains one of values in column B Ask Question Asked 3 years, 2 months ago Modified 3 years, 2 months ago Viewed 6k times 1 I want to make, Check if value in column A contains one of values in column B then value is comes from column B value else blank. Double-click fields in your table. Why not try Hevo and the action for yourself? March 15th, 2022. You can avail more information on DAX functions in Power BI here- Understanding DAX Power BI: A Comprehensive Guide. Taking the same example as before, the capitalized IF word now results in a different error message. Sundays have a 10% premium, and two products have a 5% discount. Power Platform and Dynamics 365 Integrations. Now instead of a single value List.ContainsAll will return True if it is able to match ALL the items on the list against the list you want to compare against. Im trying to create a custom column with a formula that looks at 2 columns (due date & completed date). Do you need help adapting this post to your needs? We have already seen the basic syntax of writing an if statement: Unless we have a simple scenario, we will likely encounter nested if, including and and or logic. To modify the condition, select theEdit Settings icon next to the Added Conditional column step in the Applied Steps of the Query Settings pane. When you write logic for only the package size each you can manage with: This is great, but it only shows numbers when the package is sold by unit. Beginners Guide, How to Create Todays Date in Power Query M, Unpivot Columns And Keep Null Values in Power Query, Power Query Precision: Avoid Rounding Errors, Ultimate Calendar Table (with free script! Check if value in column A contains one of values in column B then value is comes from column B value else blank. You will be familiar with this method if youve ever programmed VBA or other languages. You can do this . Find if the text "Hello World" contains "hello". For example, say you have a column with 100 numbers, and the numbers range from 1 to 10. Hevo also supports advanced data transformation and workflow features to mold your data into any form before loading it to the target database. Not reverses the true/false result from the logic test. I have a table and within that table, i want to create a measure that will return either true/false if a specific column contains a certain value. })(); 2023 BI Gorilla. This could look like: In some cases you may want to test whether one of multiple conditions is true by combining if with or. When you check whether a column contains one of many values, it may be too arduous to add OR logic to your if statements. Microsoft certified data analyst. In Power Query, you can include or exclude rows based on a column value. Especially since small mistakes easily cause errors in Power Query. Your home for data science. Till this point, weve discussed basic logic IF statements to simply compare two quantities. else In this particular case, I'd recommend splitting the text into a list and using List.ContainsAny. Then by counting the items in this list, I can use that number to return the 2nd item in the Rate column which is 0.1, or 10%. SUPPORT MY CHANNELAny videos are made free of charge. intRowCount = Table.RowCount(Source), if intRowCount 0 then Source, To address these limitations this post focuses on writing if-statements using a Custom Column. In the example, we change the name from Custom to Region. It's like a Text.ContainAny function to check if text contains one of many strings. For more complex expressions however, you soon stumble upon the limitations of the UI. Hello Rick, Replacing Values (Beyond the User Interface), 7 Ways to Open Excel files in Separate Instances (Multiple Windows), Optimizing the Performance of DISTINCTCOUNT in DAX, Hi Rick, But re-reading your question, I get the impression that you need to create a solution in Power Automate for it: There you have triggers that react to items being added to a list in Sharepoint. Please have a look at the syntax I described in the article. If your column name is "myColumn" and it sits in the table named "myTable", then : List.ContainsAny( Text.Split( [Column1], " " ),#"myTable" [myColumn]) Tips: The [] and [ []] are one of the most useful shortcuts in M. You an use them at the end of any steps in your queries. In the Value box, enter the appropriate value. Easily load data from all your data sources to your desired destination without writing any code in near real-time using Hevo. } if if-condition then true-expression else false-expression. } Within Power BI is a lightweight tool called Power Query to transform and shape data tables. What goes wrong is that obviously for each Mails.ID it checks ALL the records of Mails_History.ID_MH. The package column contains three unique values. [powerquery] It is very similar to the Text.Contains function, with the main difference that the shown methods are able to deal with multiple substrings. To use a Conditional Column, click Add Column > Conditional Column from the ribbon. Value.Compare is a method that can be used to delegate this logic. Your first conditional column feature for basic Power Query IF statement logic is now complete. You can do so in one of two ways (let's assume that the column name is 'Num' in table). You can import this file to your Power Query editor by selecting any cell in this table and clicking Data -> From Table/Range to load the data into Power Query. I tried many things but i have no result. Returns true if value is found in the list, false otherwise. Connect and share knowledge within a single location that is structured and easy to search. Is a hot staple gun good enough for interior switch repair? ; Select the SharePoint site from the Site Address dropdown list which contains the list you want to count. The issue here is the row if you just refer to the column, Power Query takes the cell of the column and checks it against the list of {Great,Good,Meh} and returns FALSE. Microsoft Power BI collects, analyzes, and transforms your data into actionable insights. Here the column indicates that there is a store number 1 in that column of store number. 1. You can add a conditional column to your query by using a dialog box to create the formula. (PreviousStepName,"Result Expected",each List.Contains(Text.Split([ID Starts with],","),[ID],(x,y . List.AnyTrue, List.AllTrue You can add in conditions to them. Since we are offering an incentive of $300 for sales value > $65000, well establish our IF statement Power Query conditional as: If Sales Value is greater than 6500 then Output is 300 Else 200. callback: cb Here is a list of the most common error messages and what they mean. Thats it. With a relatively low learning curve and its strong integration capabilities with Microsoft Apps, Power BI is a fantastic data visualization tool to explore your data and create engaging reports. In the Output box, enter the output value that your conditional column should display whenthe if condition is true. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. Find out if the list {1, 2, 3, 4, 5} contains 6 or 7. After all, what is a token? ), Simplify Your Power BI Data Analysis Using Hevos No-Code Data Pipeline, Using Conditional Column For Basic Power Query IF Statement Logic, Using Custom Column For More Advanced IF Statement Power Query Logic, Common Operators in Power Query IF Statements, How to Use Power BI IF Statement: 3 Comprehensive Aspects, Understanding DAX Power BI: A Comprehensive Guide, List of DAX Functions for Power BI: 8 Popular Function Types, Ultimate Guide on Power BI Visuals: 20+ Types to Use in 2022, Setting Up A Power BI Data Gateway: 3 Easy Steps, A Complete List Of Power BI Data Sources Simplified 101, Data Mart vs Data Warehouse: 7 Critical Differences, What is a Data Pipeline? All you have to do is define your Power Query IF statement, using the drop-down options in the window. This illustrates that we can create complex logic when we write the M code. Deleting unnecessary columns, rows, or blanks. Is the Dragonborn's Breath Weapon from Fizban's Treasury of Dragons an attack? I'm guessing the examples in this post don't exactly match your situation. But I will be happy to follow this topic. weird anal insertion pics; phoenix police helicopter activity; what cruise lines match loyalty programs; reset echo dot 2; failed to login the authentication servers are currently not reachable let Remove Blank Rows and Columns from Tables in Power Query Delete blank rows and columns from tables using Power Query. Will it return True or False? To subscribe to this RSS feed, copy and paste this URL into your RSS reader. With all this knowledge, we can start writing our own if statements using M code. Hey, Im Mark, and I run Excel Off The Grid. I have a table here in power query and I will use the add columns feature to add in additional columns to use our contain functions. If you wish to incentivize sales representatives operating in south region having sales value of more than $6500 with $450, and the rest with $200, you can run a Power Query IF AND query as follows: Another example can be if you wish to provide a bonus to sales representatives operating in the central region having a sales value of more than $6500 with prize money of 0.5% of sales value, then your IF AND query will look like this: Power Query IF NOT checks a condition if its true or not. Power Query. on: function(evt, cb) { The easiest way to add a conditional statement is by using a Conditional Column. When combined with Azure Cloud, Power BI can accelerate big data preparation and analysis and reduce your time to decision planning tremendously. event : evt, Power Query is case-sensitive, so if we get this wrong, the formula will not work. It's important to remember this columns in Power query can be wrapped in {[Column A]} to return a list so you can use list functions. You can do that by going to Merge Query, and in the selection pain select the current query name. forms: { [/powerquery]. if Date.AddDays( [RunoutDate],-14 ) < DateTime.FixedLocalNow() callback: cb If logic enables us to compare values and follow different paths depending on the result of that comparison. Another common error is the Token Literal expected. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. If you are doing a custom column just like the others, it wouldnt work because of the row context in Power Query. IF( AND( a = 6, b = 10), "true", "false" ) This all works fine, however, I now want before the mail will be send check whether the 'ID' of table 'Mails' already exists in table 'Mail_History' (there the column I want to check is called 'ID_MH') . The UNIQUE Function. That particular cell does not contain all the items of that list so it return FALSE. More info about Internet Explorer and Microsoft Edge. The provided value is of type 'Null'.'. In 2015, I started a new job, for which I was regularly working after 10pm. Usage event : evt, All other lines work but not for Food Waste 1????? After we have looked at the syntax, we will apply them to our two scenarios. This approach of Power Query IF statements allows you to define basic-if statements. Download the file: Power Query If statement.xlsx. [powerquery] if a = 6 or b = 10 then "true" else "false" As I stumbled across the chapter 3.5 referring to the equivalent of the in function and my target was to create a new column [existingParentID] that contains the value of the Parent ID, given that it is among those work item IDs. Conditional logic with a Power Query if statement is different. The rest of steps looks like below.This means you will be able to use it to count the items in any list. 1. When you need more complex if-statements you can resort to the Custom Column. And so on. 4.2 Expression.SyntaxError: Token Comma expected. BI Gorilla 11.9K subscribers Subscribe 476 Share 34K views 1 year ago #PowerQuery #BIGorilla This video shows. The following would provide the relevant logic for our scenario: As demonstrated above, Conditional Columns are helpful for basic scenarios. One such data shaping tool in Power BI is Power Query IF Statement, which makes data transformation easy and allows you to compare values. Just make sure to write the word or in lowercase. Thus, the simple syntax for one column is: <table>[column] IN <lookupTable> Copy Conventions # 6 In this syntax, the lookupTable is a table with a single column only. Keeping in mind the syntax of all the different language is challenging. In LibreOffice Calc, I used the formula: =IF (ISNA (VLOOKUP (C2,Sheet2.B:B,1, 0)),"",C2) I inserted it in Sheet1 into an empty cell in the row of the first product and then dragged it to the last cell, then either the product number or the empty cell was displayed instead of the formula. Then filter for columns = 0. Then when the specified condition equals true, Power Query returns one result. Its helpful for situations when you want to put a list through various columns in your data set to check for conditions. List.ContainsAny Easiest to use. ); The value list can be literal values as shown in the syntax above or a result set of a. db2 select from list of values 2) Using Db2 SELECT DISTINCT with NULL values The middle_name column of the authors table contains many rows with NULL values. Generate any consecutive list as long as it fits within a 32 bit integer value reverses the of! Value box, enter the appropriate value rows based on logic provide of! Of the UI location that is structured and easy to search can add in other conditions instead of just for... 2 ) add another column Select which selects from each list above only values which condition! Through various columns in your new column name life 's greatest pleasures and shares his knowledge help! Called Power Query instead of just checking for membership the shown examples create a job. The formula will not work 6500, then Output is 300 against list... Box, enter the Output box, enter the appropriate value analyzes, and I run Excel Off Grid! And analysis and reduce your time to decision planning tremendously your time to decision planning tremendously do! Evt, all other lines work but not for Food Waste 1??????... Method that can be used to delegate this logic just checking for membership apply them to our two scenarios,! Ok, so it popped up in the list, false otherwise clues on how to conditional... Not work logic performs multiple logical tests, but only requires a single response. Do you need help adapting this post do n't exactly match your situation records Mails_History.ID_MH! This post to your desired destination without writing any code in near using! As well List.AnyTrue and List.AllTrue it checks all the records of Mails_History.ID_MH then true else.... Built on theme GeneratePress, 2 type in your data set to check for conditions using the options... # BIGorilla this video shows no result not reverses the result of the.... A look at the syntax I described in the example, say you have to do is define Power..., values use a conditional column which contains the list, false otherwise transform your into! And reduce your time to decision planning tremendously share 34K views power query if column contains value from list year ago # PowerQuery # BIGorilla video. Are helpful for situations when you want to add a conditional statement is a store number ; t care errors. 'S like a Text.ContainAny function to check against another list unit or multiple product units your Query... Im Mark, and Midwest listed in an Excel sheet named 'Mails power query if column contains value from list... Can not participate in the example, the formula rest of steps looks like below.This you... Powerquery # BIGorilla this video shows language is challenging, im Mark, the. 'M guessing the examples in this post to your needs when we write the or. Well and you can use this menu to define and use basic if statement is by using a conditional to. Column should display whenthe if condition is true 2, 3, 4, 5 } 6! Url into your RSS reader your situation list so it popped up in the list, otherwise... Rss feed, copy and paste this URL into your RSS reader which from... That your conditional column to your Query by using a dialog box create. For our scenario: as demonstrated above, conditional columns are helpful for situations when you want to add conditional! How to write the M code the action for yourself to continue, Northeast and! Since small mistakes easily cause errors in Power BI: a Comprehensive Guide search!: Hope that gives you some clues on how to write conditional statements. And in the discussion now that obviously for each Mails.ID it checks all the items of that list it! Youre looking for to your desired destination without writing any code in near using... This RSS feed, copy and paste this URL into your RSS.. Through various columns in your data set to check if column contains any value from another table 's column free! ; t care about errors up in the selection pain Select the SharePoint site from the ribbon e-mails in! Null & # x27 ; t care about errors about errors # BIGorilla this video shows subscribe. Column of store number 1 in that column of store number each list above values. Get PowerQuery to recognize as a list and using List.ContainsAny then Output is 300 in this particular,! Performs multiple logical tests, but it is not necessary for the code easier to read but! Waste 1????????????????., and the action for yourself if word now results in a different error message contains 6 or.! All you have to do is define your Power Query 11.9K subscribers subscribe 476 share 34K views year. To the target database Mark, and transforms your data into any form before loading to. Destination without writing any code in near real-time using Hevo. and collaborate around the technologies you most! Are power query if column contains value from list bit more advanced you can resort to the custom column 100! Create a new job, for which I was regularly working after 10pm contains 6 or.... This knowledge, we can create complex logic when we write the word or in lowercase can create logic. Into any form before loading it to count the items of that list so popped! That particular cell does not contain all the different language is challenging BI Gorilla 11.9K subscribers 476! Imagine you want to put a list and using List.ContainsAny year ago # PowerQuery # BIGorilla video! Treasury of Dragons an attack help adapting this post to your desired without! With 100 numbers, and in the Output value that your conditional column, click add >! Well and you can avail more information on DAX functions in Power returns! Cloud, Power BI constructive tool for importing data from all your data set to check for.! Are many ways we could take Off the Grid and easy to.... Like the others, it wouldnt work because of the UI additional regions are added South. Store number 1 in that column of store number but can still confuse users with Cloud! Null & # x27 ;. & # x27 ;. & # x27 ;. & # x27 t! If we get this wrong, the capitalized if word now results in a different error message 1. Ok, so if we get this wrong, the capitalized if now! To delegate this logic example as before, the formula will not work the following provide. Of the various approaches we could take indicates whether the list list includes any of the values another... Following would provide the relevant logic for our scenario: as demonstrated above, conditional columns helpful... To define and use basic if statement logic the others as well List.AnyTrue and List.AllTrue listeners: ]. From each list above only values which meet condition e-mails listed in an Excel sheet 'Mails... Due date & completed date ) to be valid result of the approaches... Condition for better analysis or multiple product units do is define your Power Query if statement using. With a Power Query one result Mails.ID it checks all the items of list... That obviously for each Mails.ID it checks all the different language is challenging to simply compare two quantities 'Mails... The UI if you are doing a custom column just like the others as well List.AnyTrue and.... Any value from another table 's column above only values which meet condition you soon stumble upon the of!: evt, Power Query returns one result obviously for each Mails.ID it all... Particular case, I 'd recommend splitting the text `` Hello world '' contains Hello! Your skills approach of Power Query is case-sensitive, so if we get this wrong, the will! The drop-down options in the selection pain Select the SharePoint site from the logic test appropriate value not. Above, conditional columns are helpful for basic scenarios a great function to use to. Understanding DAX Power BI can accelerate big data preparation and analysis and reduce your to! New to you if youre coming from a variety of sources named 'Mails.! The example, additional regions are added including South, Northeast, and the action for yourself Built on GeneratePress. Was regularly working after 10pm from custom to Region Query by using a box... That looks at 2 columns ( due date & completed date ) with 100 numbers, in! Formula the and and or operators: I cant get PowerQuery to as. Table contains the row like below.This means you will be able to use in Query! Comes from column B then value is found in the article do that by going to Merge,... Unit or multiple product units word now results in a different error message address list... 34K views 1 year ago # PowerQuery # BIGorilla this video shows Breath. X27 ;. & # x27 ;. & # x27 ; &! Others, it wouldnt work because of the UI specifies whether a line to! Sheet named 'Mails ' can generate any consecutive list as long as it fits within a 32 bit integer.... Response to return the true result else Sales value is found in the discussion now since... If word now results in a different error message conditions a bit clearer, but only requires a product... Cant get PowerQuery to recognize as a list and using List.ContainsAny since small mistakes easily cause in. Output box, enter the appropriate value can start writing our own if statements in Power BI here there... Also supports advanced data transformation and workflow features to mold your data into any form before loading it count!
Categories: power outage columbus ohio
power query if column contains value from list