Forum Discussion

jcampbell474's avatar
jcampbell474
Helper IV
5 years ago
Solved

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....
Need Help
  • edhans's avatar
    edhans
    5 years ago

    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

     

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

    Change it to look like this:

    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:

    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.

     

     

     

edhans's avatar
edhans
Community Champion
5 years ago

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

 

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

Change it to look like this:

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:

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.

 

 

 

BLB's avatar
BLB
Helper I
3 years ago

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