Power BI - Conditional Filtering in Power Query

Introduction

Power BI offers with the Power Query Editor a powerful tool for cleaning and transforming data. One important part of data preparation is filtering your data. Filtering enables you to sort out irrelevant data and to reduce the amount of data. One important type of filtering is conditional filtering. Conditional Filtering is necessary when you want to filter data that matches a certain criteria. Furthermore, it is also possible to create AND and OR conditions for filtering. In this tutorial, we show you how to apply conditional filters in Power Query.

Data

The data for this tutorial comes from an Excel file. In the following tutorial, we have shown how to import Excel into Power BI:

We have loaded the data into Power BI but we haven't executed any transformations on the data. The data looks as follows:

Now, let's explore how to execute some conditional filter operations with Power Query.

Open Power Query Editor

First, we open the Power Query Editor by clicking on "Transform Data":

This takes us to the Power Query Editor interface:

💡

All steps that are performed are displayed on the right-hand sidebar. You have the opportunity to undo steps at any time.

Conditional Filter

Let's start with a simple condition for filtering. In the following example, we only want to include students with creditpoints higher than or equal to 80. So we need to apply a conditional filter to the column "creditpoints". To do this, we click on the down arrow of the column "creditpoints", click on "Number Filters" and select "Greater Than Or Equal To...":

Next, a new window opens in which we enter the desired threshold value. In our case, we enter 80 and click "OK":

The filtered data looks as follows:

In addition to simple conditions, we can also use AND and OR conditions.

AND Filter

Let's create an AND condition for filtering. In the following example, we only want to include female students born in 2002. So, we need to filter the data where the gender is female AND the birth year is 2002. This is an AND condition for the columns "gender" and "birthyear". To apply this kind of filter, we click on the down arrow of the column "gender", click on "Text Filters" and select "Equals...":

Next, a new window opens. We choose "Advanced" and enter the desired columns, the operators and the values. Make sure that the AND Operator is selected. As soon as we have completed the selection, we click on "OK":

The filtered data looks as follows:

OR Filter

Let's create an OR condition for filtering. In the following example, we only want to include female students born in 2002. So, we need to filter the data where the gender is female OR the birth year is 2002. This is an OR condition for the columns "gender" and "birthyear". To apply this kind of filter, we click on the down arrow of the column "gender", click on "Text Filters" and select "Equals...":

Next, a new window opens. We choose "Advanced" and enter the desired columns, the operators and the values. Make sure that the OR Operator is selected. As soon as we have completed the selection, we click on "OK":

The filtered data looks as follows:

Conclusion

Congratulations! Now you are one step closer to become an AI Expert. You have learned how to create Conditional Filters in Power Query. You have seen that you can filter your data based on simple conditions. Moreover, you can also create AND and OR conditions for filtering. Try it yourself!