Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to Solution.
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
FilterRows
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
FilterRows
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
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 Date
It might because of type of your data, please share an image of your date column
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()))
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
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 .
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
42 | |
26 | |
14 | |
14 | |
12 |