cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
richard-powerbi
Post Patron
Post Patron

Parameter from dynamic value?

For incremental refresh I want to make a parameter called RangeEnd that takes yesterday's date. I thought I could use a list query Date.AddDays(DateTime.LocalNow(), -1) to make this parameter dynamic. But I can't get it to work. Here it says dynamic parameters are not possible. Is this still true? Does anyone know a way around this?

1 ACCEPTED SOLUTION
Smauro
Super User I
Super User I

Hi @richard-powerbi 

Based on your link:

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


And test:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lcy5EYBAEMTAXNbGQMDxxLJ1+acBNcJpT+qutebSRdziHo844hmveMfH6o+tMcceB3jABT5wwneZLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    Test = Table.AddColumn(#"Changed Type", "Test", each DateTime.Date(Date.AddMonths(RangeEnd, -[Column1])), type date),
    #"Filtered Rows" = Table.SelectRows(Test, each [Test] > DateTime.Date(RangeStart) and [Test] <= DateTime.Date(RangeEnd))
in
    #"Filtered Rows"


image.png




Feel free to connect with me:
LinkedIn

View solution in original post

2 REPLIES 2
Smauro
Super User I
Super User I

Hi @richard-powerbi 

Based on your link:

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


And test:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lcy5EYBAEMTAXNbGQMDxxLJ1+acBNcJpT+qutebSRdziHo844hmveMfH6o+tMcceB3jABT5wwneZLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    Test = Table.AddColumn(#"Changed Type", "Test", each DateTime.Date(Date.AddMonths(RangeEnd, -[Column1])), type date),
    #"Filtered Rows" = Table.SelectRows(Test, each [Test] > DateTime.Date(RangeStart) and [Test] <= DateTime.Date(RangeEnd))
in
    #"Filtered Rows"


image.png




Feel free to connect with me:
LinkedIn

View solution in original post

@Smauro So I understand it correctly that the only trick you did was add the code below?

meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true]

Ahhh so it's this trick:

https://www.excelguru.ca/blog/2018/05/30/creating-dynamic-parameters-in-power-query/

 

Thanks!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors