Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Pavan_123456789
Helper II
Helper II

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.



 

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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.

vxinruzhumsft_0-1729825801916.png

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.

 

View solution in original post

10 REPLIES 10
v-xinruzhu-msft
Community Support
Community Support

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.

vxinruzhumsft_0-1729825801916.png

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
Super User
Super User

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
Omid_Motamedise
Memorable Member
Memorable Member

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

KNP
Super User
Super User

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.

 

KNP_0-1729662302109.png

 

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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 .

Pavan_123456789_1-1729663743797.png

 

Vijay_A_Verma
Super User
Super User

Change Range Start to 

Date.StartOfYear(DateTime.LocalNow())

Hi @Vijay_A_Verma ,

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

Pavan_123456789_0-1729663598703.png

 

 

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.