Only Get the Last Few Periods of Data into Power BI using Power Query Filtering

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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
  • Excel
  • Power BI Dataflows
  • Power Query Source for SSIS (and ADF)

Summary

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:

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

9 thoughts on “Only Get the Last Few Periods of Data into Power BI using Power Query Filtering

    • Hi Ramani.
      That is exactly what this filtering option will do. It will be always three years back from the date of a refresh. so will be rolling all the time.

      Cheers
      Reza

  • I have a dataset with 1.5mil rows and a set of 20 columns.
    as expected it takes a while to load the data (a couple 100records a second) but when I would filter on a date field in the query it takes even longer! why is that and how to workaround?

    • Hi Johan
      There can be many reasons that cause the load process to be slow, here are some: complex data transformations, the volume of data (rows and columns), the data source itself (excel file, database, etc)…
      You mentioned that it becomes even slower after putting a filter. I believe the data source is something that doesn’t support query folding then. probably it accesses the entire data first, and then filter it. If it was a database system or something that would have supported query folding, it would have even become faster. Also, other transformations in Power Query matters too. they might cause stopping the folding to happen.
      Cheers
      Reza

  • Hello Reza,
    Nice thing to know Radcad. I am using a table which has 1 date column and we are having some 15 years of transactional Data. How can i filter the data before load based on Fiscal Years, lets say from FY start date till today. We maintain Our Fiscal Year start and end date in a seperate table.

    • Hi Ali
      everything that I mentioned in this post will be filtered BEFORE loading data into the model.
      if you want to do this filtering based your fiscal year, you can go two years back, and then find the start of the fiscal year for that period (based on whatever logic calculated), and then filter everything after that. It would need a custom column to be added in Power Query first, and then filtering based on that. Here I explained a bit about how you can have the fiscal calculation
      Cheers
      Reza

Leave a Reply

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