Assistance with Dynamic Parameters in Power Query

[Pavan_123456789]

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.

[v-xinruzhu-msft · Accepted 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.

[KNP]

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.

[Omid_Motamedise]

Use this formula

= List.DateTimes(Date.StartOfYear(DateTime.LocalNow()),Duration.Days(DateTime.LocalNow()-DateTime.From(Date.StartOfYear(DateTime.LocalNow()))),#duration(1,0,0,0))

[Pavan_123456789]

HI @Omid_Motamedise 

I have tried this still i am getting same issue Enter valid Date

[Omid_Motamedise]

It might because of type of your data, please  share an image of your date column

[KNP]

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()))

[Pavan_123456789]

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 .

[Vijay_A_Verma]

Change Range Start to 

Date.StartOfYear(DateTime.LocalNow())

[Pavan_123456789]

Hi @Vijay_A_Verma ,

I have tried this still i am getting the error Enter valid Date

[Vijay_A_Verma]

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.