How to Dynamically Restrict Data Import in Power Query

Author:Murphy | View: 29252 | Time: 2025-03-23 11:56:35

When we store a large amount of data, we might ask ourselves: Do I really need all this data in my reporting? If you know this question and want to learn how to control the amount of data in your Power BI file, this is the one piece to read.

Introduction

My largest data set contains 20 years' worth of data.

While this is good when I want to perform some tests with a large data set, it's not uncommon for a report to need more than a few years of historical data.

My clients usually want no more than two or three years of data.

And now comes the challenge:

How can I control the time frame when importing the data into Power Bi?

This is not a big deal when working with SQL databases or accessing a data source that can be controlled programmatically or by passing parameters, such as a web-based REST API.

However, sometimes, I must control this myself. In such a case, I must do it in Power Query.

Ideally, I want to build it once, and it will run forever without any further intervention.

This is what this piece is all about.

Preparation

If you haven't already, enable the formula bar in Power Query to follow my instructions.

Open the Settings of Power BI and switch to the Power Query Editor page.

There, enable the "Display the Formula Bar" option:

Figure 1 – Enable the Formula Bar for the Power Query Editor (Figure by the Author)

Create the dynamic parameters

OK, the title is a little bit misleading.

Power Query parameters are not dynamic per se.

They can be based on a Query, but I cannot set the value with an expression, even though the Query can be dynamic or data-driven.

So, I must build one or two queries, which can then be used to filter our data.

When the data contains data up to a point in time that is useful for the intended Reporting targets, I need one query for the earliest date to import.

I need two queries if I need to restrict the start and the end date, for example, the date of the latest data point of interest.

Let's assume that I want to load data from the beginning of the year, two years ago, to the end of the previous month: From the beginning of 2022 to the end of July 2024.

Let's do it.

After opening Power Query, I create a new blank query:

Figure 2 – Create a blank query in Power query (Figure by the Author)

This will contain the start date for my data.

I will use the M-functions #date, Date.Year and DateTime.LocalNew to set it:

#date(Date.Year(DateTime.LocalNow())-2, 1, 1)

DateTime.LocalNow() gets the current date and time
Date.Year() extract only the year from the given date
date() creates a date from the parameters for the year, month, and day

As I subtract two from the year, I get 2022 as the year, and with the following two parameters, I will get 2022–01–01.

But the result is not as expected:

Figure 3 – Result of the M-expression if the Blank Query (Figure by the Author)

When we open the Advanced Editor, we see that Power Query has set the expression as a string.

We must remove the quotes, and it will work:

Figure 4 – Remove the Quotes to set the expression as intended (Figure by the Author)

But, before I continue, I change the Query name from Query1 to "StartDate".

Next, I create a second Blank Query (Or I duplicate the first one) and set the following expression in the same way as the one above:

Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -1))

DateTime.LocalNow gets the current date and time
Date.AddMonths() add months to the given date. In this case, it goes back for one month.
Date.EndOfMonth() shifts the date to the last day of the given month.

In my case, I will get 2024–07–31.

This is the Result:

Figure 5 – Result of the Expression to get the End Date (Figure by the Author)

As I need only the date, I can remove the time part by transforming the result to a Date:

Figure 6 – Transform the Date/Time output to a Date only (Figure by the Author)

After renaming the new Query, everything is ready to filter the Data based on these two queries.

Filter the data

Next, I can add a filter expression to my Fact tables.

I find the Date column for my data and select a between filter:

Figure 7 – Select the Between filter for the Date column (Figure by the Author)

Next, I enter two arbitrary dates in the two fields (I will replace them with the newly created queries afterward):

Figure 8 – Add arbitrary dates in the between filter (Figure by the Author)

After clicking on OK, the data is already filtered.

But now, I want to change the filter to use the two queries.

For this, I replace the dates in the filter Expression to use them:

When I look at the Formula Bar at the top of the Power Query Editor, I see the expression for the selected step.

I change the expression from this:

Table.SelectRows(#"Renamed Columns", each [Date] >= #date(2024, 8, 1) and [Date] <= #date(2024, 8, 31))

To this:

Table.SelectRows(#"Renamed Columns", each [Date] >= StartDate and [Date] <= EndDate)

Done.

As I have two fact tables, I copy this expression and add it to the other table:

I select the table, and I click on the fx button. After this, I can paste the same expression as before into the formula bar:

Figure 9 – Add a new Expression to filter the second table (Figure by the Author)

Lastly, I change the name of the Step to something meaningful on both tables:

Figure 10 – Change the name of the Step to filter the data (Figure by the Author)

Add a Parameter to control the amount of Years

Finally, I can add a Power Query parameter to control the years I want to import in Power BI.

Figure 11 – Create a new Parameter to store the number of years (Figure by the Author)

I enter a Name, set the type to Decimal Number, and enter 2 for the Current Value:

Figure 12 – Set the new parameter (Figure by the Author)

Next, I change the M-Expression for the StartDate Query to use this parameter:

#date(Date.Year(DateTime.LocalNow())- NumberOfYearsToImport, 1, 1)

The result doesn't change, as I used two before.

Now, I can change the parameter from Power BI and the amount of data without changing the expression in Power Query.

Furthermore, I can change this parameter after publishing the Report on the Power BI Cloud:

Open the Settings of the Semantic Model, open the "Parameters" Section, and change the value.

After loading the data in Power BI, I see only data from the 1st of January 2022 up to the End of July 2024:

Figure 13 – Filtered Data after loading it into Power BI (Figure by the Author)

And what about a DateId?

The above expressions will no longer work if your data doesn't contain a Date column but a numerical DateId column.

For those unfamiliar with a DateId, this is a numerical representation of a Date.

For example, 2024–07–15 is translated to 20240715.

In such a case, use these expressions to calculate the DateId:

For StartDate:

(Date.Year(DateTime.LocalNow())- NumberOfYearsToImport) * 10000 + 101

For the EndDate, here is the entire M-Script (Replace the Script into the Advanced Editor after creating a Blank Query):

let
Source = Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -1)),
#"Extracted Date" = Date.Year(Date.From(Source)) * 10000 + Date.Month(Date.From(Source)) * 100 + Date.Day(Date.From(Source))
in
#"Extracted Date"

The data filtering is the same as above but with numerical values instead of Dates.

Turn off the Load for the new Queries

Without any further steps, the two new queries are loaded into Power BI:

Figure 14 – Start- and EndDate tables in Power BI (Figure by the Author)

As these two tables are of little use in Power BI (Except if you want to show these two values in the report as information), I will Disable the Load in Power Query for both tables:

Figure 15 – Disable the load into Power BI of the unneeded tables (Figure by the Author)

You will get a Warning about a possible data loss. You can acknowledge it.

The names of the tables will turn in cursive, and they are not loaded into Power BI anymore.

Conclusion

The approach shown here is very useful for dynamically defining the time frame of data imported into Power BI.

In my case, I use a SQL Server as my Source.

Power Query can translate the data filtering to SQL and send (fold) the query to the Source.

If you want to learn more about Query folding, read this:

Exploring Query Folding in Power Query

However, the general approach works with any other data source as well.

But be aware: If Power Query cannot pass the filter to the Source, it will first load the entire data set and filter out the unwanted rows after loading all the data.

This can be a problem if you have a vast amount of data.

As useful as this approach is, having only the needed data in the source is essential. Please ask your data provider for help.

However, it may be helpful to restrict the amount of data loaded into Power BI during development and load the entire data set after the solution is published.

In any case, I hope that you have learned something new about Power Query.

References

Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be freely used under the MIT License, as described here.

I enlarged the dataset to make the DAX engine work harder. The Online Sales table contains 71 million rows (instead of 12.6 million rows), and the Retail Sales table contains 18.5 million rows (instead of 3.4 million rows).

Get an email whenever Salvatore Cagliari publishes.

I make my articles accessible to everyone, even though Medium has a paywall. This allows me to earn a little for each reader, but I turn it off so you can read my pieces without cost.

You can support my work, which I do during my free time, through

https://buymeacoffee.com/salvatorecagliari

Or scan this QR Code: