Forum Discussion

Pavan_123456789's avatar
Pavan_123456789
Helper III
1 year ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
1 year ago

Change Range Start to 

Date.StartOfYear(DateTime.LocalNow())
Pavan_123456789's avatar
Pavan_123456789
Helper III
1 year ago

Hi Vijay_A_Verma ,

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

 

 

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    1 year ago

    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.