Sometimes when you work within power query, you need to filter a column based on some values either to exclude them from the column or to include them, although you can do it manually, it might be tiring especially if you have many values.
In this article I will describe how to filter and add conditional column using a list and M code in power query.
Let’s suppose you have a Product subcategory table and you want to filter it, either to keep the desired values or to remove the unwanted ones.
First thing, you need to create a list containing the values you want to use in the filter. To do so, you prepare your table in a csv file or any other supported source in Power BI and import it to power query, then in the Transform tab, select "Convet to list" which will convert the table to a list.
Next you add a new step in the Product subcategory table, then write the M code below:
Table.SelectRows(#"Changed Type", each List.Contains(#"Subcategory to be deleted", [ProductSubcategory]))
The Table.SelectRows() is used to select rows that meet a condition, its first parameter is a table which is in our case the previous step, for the second parameter we’re going to use List.Contains() function.
The List.Contains() indicates if a list contains a value, its first parameter is the created list "subcategory to be deleted" and the value is the column to filter.
When you validate the code, you will get the result, a filtred column. The table keeps only the values in the list.
If you want instead to keep the values to exclude them, just add "not" before the List.Contains()
Table.SelectRows(#"Changed Type", each not List.Contains(#"Subcategory to be deleted", [ProductSubcategory]))
Another example, you can create a conditional column based on the list. Let’s suppose we want to create a new column which is null if the value of subcategory exists in the list otherwise, we keep the value of subcategroy.
First you add a custom column, then in the formula you write
if List.Contains(#"Subcategory to be deleted", [ProductSubcategory]) then null else [ProductSubcategory]
As you can see below, we get the required result.
For more details, check out the video in the "Related Video" section below.
Related Video:
