Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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,
Solved! Go to 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
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,
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.
Thanks for the clarification!
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
This is fantatsic solution.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
17 | |
9 | |
8 | |
7 | |
7 |