Power BI reports can get slow if you have huge amount of data. One of the ways to speed up the performance is to get the smaller part of the data, only the part that is needed for analysis. Most of the times, a date condition is a good filter for that. You might only want to analyze the last three years of data, or the last 6 quarters, or everything after a specific date. In Power BI this is simply possible using Power Query and filtering on the date field. In this short article, I’m going to explain how to do that. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.
Date Based Criteria
The date is one of the most important factors for filtering your data. Most of the time, you want to analyze only part of the data, and that part of the data has something to do with the dates. For example; you might have 20 years of transactional data stored in the database, but you only want to do an analysis of the last three years. Or because the data has been migrated from an old system, the better quality data you’ve got is after a specific date, and you want only to do analysis for that part of the data. In my experience, a date based filtering is one of the most common scenarios happening in an analysis system.
Power Query: Transforming Data Before Load
When you use Power BI (at least these days), the default option after selecting your tables is Load. I never use that option! The reason is that you are getting the entire table without any filtering or transformation. What I always do is to use Transform Data (It was called Edit previously).
If you don’t use this option, and just load data directly, then the entire data rows in that table (it might be 20 years of data or 50 million rows), will be loaded into the memory. Which is most probably what you do NOT want to happen.
If you click on Transform Data, then you get into the Power Query Editor window, where you can filter the data table BEFORE loading it into the memory. But here is an important tip.
When you see the data rows in Power Query Editor, you might think that the data is already loaded into the memory, and you want the filtering to happen beforehand. However, remember that Power Query Editor is working on a PREVIEW of data (it can be 1000 rows, or 200 rows, based on the number of columns, but it will be always preview), and not the entire dataset. After doing all the transformation in Power Query Editor, when you click on Close & Apply, at that moment, the entire REMAINING data after filtering will be loaded into Power BI.
Date Based Filtering in Power Query
To filter a column in Power Query, very similar to Excel, you can click on the column header and see all the filtering options
There are many options for filtering based on a date and time field;
In the Previous Periods (Years, Quarters, Month, Days) Filtering
One of the most common ways of filtering the date field is based on the previous periods, you can choose In the Previous
Then you can specify the period and how many occurrences of that period you want as below;
This would create logic in M script that always compares the date in the field of that table with the date and time of refreshing the Power BI dataset. It means that, after publishing this Power BI file to the service and scheduling it to refresh, it will be always the last three years of data from the time of refreshing the report.
After, Before, or Between
Another useful filtering is to get data rows only after a specific date and time, or before, or even between a start and end date. This is particularly useful when you want to filter out part of the data table because the quality of data was not good up until that date and time, or something happened in the business at a certain date (or period) and you only care about analyzing data after that or within that period.
Then you can choose dates in the Filter Rows section.
On important thing to know is that you can also use Power Query parameters to parametrize the values you use for filtering.
There are many other ways of filtering based on a date field, which you can explore
I second that again that any filtering you apply in the Power Query Editor, will be applied on the dataset BEFORE loading it to the data model, and as a result, it will reduce the memory consumption, and speed up the overall performance of your model.
Not Just for Power BI
Remember, this is not just to be used in Power BI. It can be used anywhere that you have Power Query access, which means:
- Analysis Services
- Power BI Dataflows
- Power Query Source for SSIS (and ADF)
There are many reasons why a report is slow. I have explained some of them in other articles, which you can find their links down below. Loading the entire dataset without filtering is one of them. In this article, you learned how easy it is to filter your data rows based on a date filter. Is there any other ways that you want to filter your dataset but you don’t know how to do it? ask me in the comments below.
Other resources to read if you want to speed up your report: