How to populate current value in a parameter using a query (only 1 in the list)

[rjs2]

I am close to solving a problem.  I have greated a query that generates todays date and subracts 45 days from today. Then I coverted the query to a list and have it set for a parameter.  The problem I am having is that I can not set the current (no default option on parameter) to the value in the list.  The list only contains on option, which is the date 45 days ago.

How can I populate the parameter?

[LeoGorkes]

@PhilipTreacy 
Did you find solutions about this topic?
i was unable to implement the above solution.

best regards

[Icey · Accepted solution]

Hi @rjs2 ,

It is not supported to enter dynamic value in "Current Value" field of parameter. One workaround is to create a list with "Today" like so:

let
    Source = List.Dates(Date.From(DateTime.LocalNow()),45,#duration(-1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each if [Column1] = Date.From(DateTime.LocalNow()) then "Today" else [Column1]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}})
in
    #"Changed Type"

Refer to the modified parameter:

    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ( let ModifiedParameter = if Parameter1 = "Today" then Date.From(DateTime.LocalNow()) else Date.From(Parameter1) in [Column1] = ModifiedParameter))

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

Hi @rjs2 

Well the way it's supposed to work is that in the Manage Parameters settings, you set it to use your query as the Suggested Value(s)

But it will not work as expected for me.

But i you just want to use the result of your query in another query, just use the first query name to get that result, e.g.

Source = Query1

That said, why do you need a separate query to get today's date - 45 days?  You can do this directly in any query like so

let 
    Source = "...",
    Today_Minus_45 = Date.From(Date.AddDays(DateTime.LocalNow(), -45)),
    ........
in 
    LastStepName

Today_Minus_45 can be used anywhere in that query once it's created.

Regards

Phil