Dynamic parameter in Power Query

[一棵黄葛树]

Can a new parameter in Power Query be a dynamic date? Such as:
Dynamically return the last month-end date of the month in which today falls: = Date.AddDays(Date.StartOfMonth(DateTime.LocalNow()), -1))

[v-pnaroju-msft · Accepted solution]

Hi 一棵黄葛树,

Thank you for the update. We sincerely apologise for the inconvenience caused.

Please find attached the PBIX file and a screenshot for your reference, which may assist in resolving the issue.

If you find our response helpful, kindly mark it as the accepted solution and consider providing kudos. This will assist other community members facing similar queries.

Thank you.

[一棵黄葛树]

Thank you very much. Perfect has solved my problem and I feel the continuous attention and patient explanation. Previously, I misunderstood that it was creating a new step, not a new query.

[v-pnaroju-msft]

Hi 一棵黄葛树,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[一棵黄葛树]

The problem still hasn't been resolved. Previously, AlienSX provided me with a solution, but I don't know how to operate it. Maybe because I don't have enough permissions, I can't upload attachments. Please ask him to help me take a look.

[v-pnaroju-msft]

Thank you, @AlienSx  and @Omid_Motamedise , for your response.

Hi 一棵黄葛树,

We sincerely appreciate your inquiry on the Microsoft Fabric Community Forum.

As per my understanding, if we intend to use the parameter, we cannot input code in the 'Current Value' field of the Manage Parameters screen; instead, we must enter the date.

As an alternative, if we wish to use the code, we need to define the parameter separately in the Advanced Editor and then reference it accordingly. Kindly refer to the attached sample code and images provided below:

let
Source = Csv.Document(File.Contents("C:\Users\v-pnaroju\Downloads\sampledata.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type datetime}, {"Sales", Int64.Type}}),
MyParam=Date.AddDays(Date.StartOfMonth(DateTime.LocalNow()), -1),
FilterRows=Table.SelectRows(#"Changed Type",each [Date]<MyParam)
in
FilterRows

Additionally, please find below the reference links for your convenience:
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
Parameters - Power Query | Microsoft Learn

If you find our response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will assist other community members encountering similar queries.

Thank you.

[Omid_Motamedise]

Yes, 
see the following:

let
Today = DateTime.LocalNow(),
StartOfCurrentMonth = Date.StartOfMonth(Today),
LastMonthEnd = Date.AddDays(StartOfCurrentMonth, -1)
in
LastMonthEnd

[一棵黄葛树]

Thanks, the new query is fine, my need is for new parameters. Or how to set the dynamic date in the parameter?

[AlienSx]

Date.AddDays(Date.StartOfMonth(DateTime.LocalNow()), -1) meta [IsParameterQuery=true, IsParameterQueryRequired=true]

[一棵黄葛树]

Thanks for the answer, but I don't know where to add this meta, could you make a PBIX attachment for me, much appreciated!

[AlienSx]

create blank query and paste this code over there

[一棵黄葛树]

Thanks for replying again, maybe I wasn't clear before.
I was hoping to create a new parameter in Power Query (dynamically display a date, e.g. if today is 2025/2/21, then the date at the end of the last month will be: 2025/1/31), and then use to call that parameter in other queries.
Such as table table, filter 20251/1/31 before all rows of data.