I’ve taken Paul Turley’s excellent advice to speed development by creating date range parameters, i.e., pDateStart and pDateEnd, and filtering the fact table to the small range of dates between those parameters so that experimental PQ changes can be applied relatively quickly.
Now that I’ve finished the development of an iteration, I’d like to temporarily change the pDateEnd value to be open-ended or today, but since the parameter needs a date data type, I can’t think of how to accomplish that.
I realize that an option could be to delete, comment out or change the filter on the fact table that uses the parameter, but I’ll want to use it again when I start the next iteration, so ideally, I’d like to leave the filter intact.
The DateTime.FixedLocalNow expression, will return a DateTime value set to the current date and time on the system. When combined with Date.From it is converted into a Date value. To illustrate:
Today = Date.From( DateTime.FixedLocalNow())
Some useful date related functions are highlighted here:
I wanted to use that Today… expression you suggested but couldn’t figure out how to get it to work in the context of the pDateEnd parameter that was looking for a date data type.
But I’ll read the article you brought to my attention and see how it might apply and I’ll let you know how I get on.
If you are using a parameter, for the sole purpose to be able to adjust its value from outside the Power Query editor then you’ll have to make some changes.
Here’s the process:
Go to manage parameters, select: pDateEnd and disable “Required”
Create a new Query, call that “Today” and enter the expression I provided earlier.
Create a new Query, call that “pDateEndSwitch” and enter this expression = pDateEnd ?? Today
Instead of pDateEnd implement the pDateEndSwitch within your code. Now, when you clear the value for pDateEnd, it returns a null but because of the coalesce operator (??) in pDateEndSwitch it will return the value for Today instead.
It’s an elegant solution to a recommended development practice that I’ll use often and highly recommend to other analysts who want to work with a temporarily reduced-size dataset while developing.