Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jcampbell474
Helper IV
Helper IV

Dynamic Date Parameter for Import Queries

I've searched everywhere for a way to do this.  Found a couple of methods that seemed too complex for what I hope is something simple.

 

I need to apply a dynamic date filter to import SQL queries.  Just need to pull in a rolling 12-month period and don't want apply it to views and stored procedures.  Is there a simple way to do it in PBI?  I created a list table, made a parameter, then filtered the table to >= the parameter.  Doesnt' work.  Looks like it's due to the Current Value being null.

 

Any help will be greatly appreciated!

1 ACCEPTED SOLUTION
edhans
Super User
Super User

Yes

First, create a new blank query in Power Query. Use this as the formula. This assumes SQL is using a date field.

= Date.AddYears(DateTime.Date(DateTime.LocalNow()), -1)

This will create a scalar value of 10/12/2019 - one year ago. (US Date format - your format might show 19/10/2019 depending on system setttings.)

  1. Rename the query varStartDate
  2. Right-click on the query and uncheck "Enable Load"

Then, in your SQL table, filter the date to whatever you want. Doesn't matter. You just want the code to  be generated. It might look like this

edhans_1-1602528606822.png

 

edhans_0-1602528551376.png

now you have a Table.SelectRows() that looks like this in the formula bar:

edhans_2-1602528669917.png

Change it to look like this:

edhans_3-1602528737684.png

You are getting rid of the date you used to create the filter with varStartDate - which is a dynamically calculated date. It will change every day.

If this is running against a SQL server, this has the added benefit of folding the query, so the server gets this code:

edhans_4-1602528831006.png

Every time it runs, it will dynamically calculate varStartDate, then will change the native query for SQL Server to use that date. Tomorrow, it would change it to '2019-10-13 00:00:00' and so on.

 

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
edhans
Super User
Super User

Yes

First, create a new blank query in Power Query. Use this as the formula. This assumes SQL is using a date field.

= Date.AddYears(DateTime.Date(DateTime.LocalNow()), -1)

This will create a scalar value of 10/12/2019 - one year ago. (US Date format - your format might show 19/10/2019 depending on system setttings.)

  1. Rename the query varStartDate
  2. Right-click on the query and uncheck "Enable Load"

Then, in your SQL table, filter the date to whatever you want. Doesn't matter. You just want the code to  be generated. It might look like this

edhans_1-1602528606822.png

 

edhans_0-1602528551376.png

now you have a Table.SelectRows() that looks like this in the formula bar:

edhans_2-1602528669917.png

Change it to look like this:

edhans_3-1602528737684.png

You are getting rid of the date you used to create the filter with varStartDate - which is a dynamically calculated date. It will change every day.

If this is running against a SQL server, this has the added benefit of folding the query, so the server gets this code:

edhans_4-1602528831006.png

Every time it runs, it will dynamically calculate varStartDate, then will change the native query for SQL Server to use that date. Tomorrow, it would change it to '2019-10-13 00:00:00' and so on.

 

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thank you for sharing it. 

Could you advise if the query would be still folded in the following scenario?

My date column comes through in a strange format which is not recognized in PowerQuery as date, only as text. So I add a new calculated column to extract the date from that original column and format it as date, and then apply the parameter as a filter in that new, calculated column. 

thanks

Perfect!  Worked like a charm!!  Thank you!!!  

 

Hopefully, someday, implementing date parameters will be easier.  🙂

Agreed! Even here at the end of 2023 Dynamic Paramters are hard to implentment into Import Queries.

Glad to help out @jcampbell474 .



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.