In this blog, we’re going to learn about some essential Power BI filtering techniques for tables. We’ll discuss some simple techniques that we can utilize when we have massive tables on our query.Â
For example, when we need to extract information from a database or an ERP system, it could have 10 million rows within that data set. Also, there could be a lot of rows that we won’t need for our analysis. That’s where the table filtering technique can become helpful.
Learning Power BI filtering techniques allows us to filter the table or the raw data source that we’re querying down to a subset of information.
We can easily access our filtering options by clicking the dropdown arrow beside our columns.
Power BI Filtering Using Text Filters
For this example, we’ll filter down the Channel column by clicking on its corresponding dropdown arrow.
As you can see, we have a lot of data in this column. However, we only want to see or show the International sales. To do that, we just need to untick the Distributor and Wholesale options under the Text Filters. Then click the OK button.
By doing that, it’ll only display the data that are on the International channel.
This table originally has 8000 rows. We can check the number of rows after filtering by clicking the Count Rows option under the Transformation tab.
As a result, our rows were reduced from 8000 to only 1196 rows.
Power BI Filtering Using Date Filters
Another good filtering technique that we could do is filtering dates. Our databases are usually structured to capture every piece of information. In this case, our databases mostly contain massive amounts of information. However, most of them are irrelevant for our analysis.
What we can do is to filter a date column by Date Filters. For this example, we’ll filter our Order Data column. First, click the corresponding dropdown icon, click the Date Filters, then select the After… option.
After that, we’ll filter this column by only showing information after 1/1/2015. So, let’s select 1/1/2015 within the date input. Then click the OK button.
Now we can see that this table only shows our data starting from 2/01/2015.
If we take a look again at the current number of rows by clicking the Count Rows option, we’ll see that we have a smaller number of rows or datasets compared to earlier counts.
***** Related Links *****
How To Use Simple Filters In Power BI
Power BI Column Charts & Filters
Dynamic Navigation Menu Using Power BI Filters
Conclusion
In summary, we were able to utilize these table filtering techniques that we could use in our Power Query tables. By trying this feature, we can have a vision of how our model is going to be built. This is because we could optimize our table first and filter out all the information that we don’t need. It’ll then help us make our model more efficient and more optimized.
This is another important part of what we’re teaching on our Data Transformation and Modeling course which you can check out in the list of relevant links below.
All the best,
Sam
