Welcome to Excel Jet Consult blog post. In this blog post, we will learn how to filter multiple columns at once using Power Query
Sample Data
In the caption below, we have sales record that is officially formatted as an Excel tables
Load Data To Power Query
Next, we want to get the dataset into the Power Query editor. To do that:
- Select a single cell inside the dataset
- In the Get & Transform group of the Data tab, click on From Table/Range
The dataset is loaded to the Power Query as seen in the caption below
Next, we are going to write a simple M Code using list.ContainsAny M Function. To do that,
- In the General group of the Add Column tab, click on Custom Column
- In the Custom Column dialogue box, execute the following like of M code
=List.ContainsAny({[2016],[2017],[2018],[2019],[2020],[2021]},{“Alpha”, “Beta”})
- Click OK
From the caption below, we can see that rows that contains either Alpha or Beta returned True based on the custom column output.
What we going to do as the final step is to filter out the rows that returned True. After doing that, we should a clean dataset as seen below
See you in the next tutorial