Power Query: Filter Multiple Columns at Once

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

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *