Forum Discussion
Assistance with Dynamic Parameters in Power Query
I am working on creating dynamic parameters in Power Query: specifically, Range Start and Range End. My goal is to have Range Start represent the start of the current year (e.g., 01-01-2024 00:00:00 AM) and Range End reflect the current date and time (e.g., 10-23-2024 10:30:00 AM).
I attempted the following M Query but encountered an error asking for a valid date value:
Range Start = DateTime.From(Date.StartOfMonth(DateTime.LocalNow()))
Range End = DateTime.LocalNow()
Could you please provide guidance on how to resolve this issue or suggest an alternative approach?
Thank you for your assistance, and please feel free to correct me if my understanding or approach is incorrect.
Hi,
Thanks for the solutions Omid_Motamedise ， KNP and Vijay_A_Verma offered, and i want to offer some more information for user to refer to.
hello Pavan_123456789 , as the users mentined, if you use the paramater , you cannot input code in "Current value",you need to input the date.
If you want to use the code, you need to set the parameter in Advanche de Eddito separately and then reference it, you can refer to the code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY31DdUitWBckz1TWAcEyDHDMEx1DdF4hjqGynFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), Rangestart=Date.From(Date.StartOfYear(DateTime.LocalNow())), Rangeend=Date.From(DateTime.LocalNow()), FilterRows=Table.SelectRows(#"Changed Type",each ([Date]>=Rangestart) and ([Date]<=Rangeend)) in FilterRowsBest Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 Replies
Hi Pavan_123456789 ,
You can't enter formulas into the 'Current Value' box, you need to enter that in a separate query and then reference that query in the parameter, but, as Vijay_A_Verma pointed out, there doesn't seem to be any reason to setup a parameter.
A query by itself would be fine.
Can you give more info about how you're trying to implement this.
Show us your code that you want to implement these parameters in, hide anything sensitive.
Hi,
Thanks for the solutions Omid_Motamedise ， KNP and Vijay_A_Verma offered, and i want to offer some more information for user to refer to.
hello Pavan_123456789 , as the users mentined, if you use the paramater , you cannot input code in "Current value",you need to input the date.
If you want to use the code, you need to set the parameter in Advanche de Eddito separately and then reference it, you can refer to the code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY31DdUitWBckz1TWAcEyDHDMEx1DdF4hjqGynFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), Rangestart=Date.From(Date.StartOfYear(DateTime.LocalNow())), Rangeend=Date.From(DateTime.LocalNow()), FilterRows=Table.SelectRows(#"Changed Type",each ([Date]>=Rangestart) and ([Date]<=Rangeend)) in FilterRowsBest Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Change Range Start to
Date.StartOfYear(DateTime.LocalNow())In your case, parameters are not needed as these values are not changed manually by the user.
Both Range Start and Range End are auto calculated by the Query, parameter has no role to play.
You should really consider whether the design you are adopting is correct or not.
Parameters are useful when manual selection is done or there is some other design condition which allows users to interact with parameters.
Having said that to create this kind of parameter, you need to put following in a blank query
={Date.StartOfYear(DateTime.LocalNow()))}
Then choose Query in Suggested Value box, followed by Query name which was created above in Query box. Put any date/time value in Current Value box.
You will need to refer to Query1{0} in your master query.
But this is, anyway, not useful for you.
Couple of notes, avoid using Range Start/End if there's any chance you may want to do incremental refresh.
RangeStart and RangeEnd are reserved for that purpose.
That aside, it would be good to see how you've created the parameters and what you're comparing it to.
It could be that you're comparing datetime to date, which you can't do.
It could be that the parameter is not setup correctly. Need some more info.
Also, for start of current year, I think you'll want...
= DateTime.From(Date.StartOfYear(DateTime.LocalNow()))Hi KNP ,
Thank you for your prompt response.
I wanted to clarify that the Range Start and Range End parameters are essential and cannot be omitted under any circumstances. I am comparing the data specifically with the date, and even using the date format is acceptable.Parameters also required in dates only not required the time .
Use this formula
= List.DateTimes(Date.StartOfYear(DateTime.LocalNow()),Duration.Days(DateTime.LocalNow()-DateTime.From(Date.StartOfYear(DateTime.LocalNow()))),#duration(1,0,0,0))HI Omid_Motamedise
I have tried this still i am getting same issue Enter valid DateIt might because of type of your data, please share an image of your date column
