Convert to Parameter Greyed Out

[AspasiaLiz]

Hi, I'd like to pull Odata Web Services from Business Central - Item Ledger Entry table but I want to pull last 12 months so that it doesn't take forever to pull. I have created a table and made that into two sets of dates in text to use as a parameter so that I can pull this automatically without touching the dates every month but I just cannot make this into parameter?

I've also tried to create new parameter through query but they do not show up.

How do I accomplish this? I don't want to put the dates manually every time I run this?

Thanks, 

[AspasiaLiz]

Thanks so much! Mine is derived from a query in a table in excel so that the user possibly change the date if needed-  then was into a text in the end. In the end it was just a text form and thought that is not complex anymore. So although the end result is just a text,  it doesn't allow it to be parameter and the parameter can only o be purely from PQ itself?

Thanks,

[OwenAuger]

You're welcome 🙂

Thanks for the additional info.
The key thing is that the interface is set up so that "Convert to Parameter" option is only enabled for "literal" values, i.e. hard-coded numbers, date, text etc.

Even if the result of your query is a single text value, if it is not a literal text value, "Convert to Parameter" is not available.

So the only way to create a dynamic parameter is to add the metadata as I showed in the previous post.

[AspasiaLiz]

Thanks for the clarification!

[OwenAuger · Accepted solution]

Hi @AspasiaLiz 

The "Convert to Parameter" option in the interface is only available for values that are a "simple non-structured constant" (see here).

In your case, if FromDate and ToDate are more complex queries, you would have to edit the code in the Advanced Editor and add appropriate metadata to turn these into parameters.

For example, if ToDate is defined as:

let
    TodayDate = Date.From(DateTime.FixedLocalNow()),
    DateEnd_Date = Date.EndOfMonth(Date.AddMonths(TodayDate,-1)),
    DateEnd_Text = Date.ToText(DateEnd_Date, "yyyy-MM-dd")
in
    DateEnd_Text

then you can convert it to a parameter by editing the code as follows, wrapping in brackets and adding meta [...]:

(
    let
       TodayDate = Date.From(DateTime.FixedLocalNow()),
       DateEnd_Date = Date.EndOfMonth(Date.AddMonths(TodayDate,-1)),
        DateEnd_Text = Date.ToText(DateEnd_Date, "yyyy-MM-dd")
    in
        DateEnd_Text
)
meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=false]

I suggest specifying IsParameterQueryRequired = false otherwise the interface shows an error in certain places.

As a side note, if another query is referencing FromDate or ToDate, it shouldn't matter whether or not they are parameters, as the value will still be the same.

See Ben Gribaudo's blog for more detail on metadata:

https://bengribaudo.com/blog/2021/03/17/5523/power-query-m-primer-part20-metadata

Does the above help with what you're doing?

Regards

[CeeVee33]

This is fantatsic solution.