Parameter from dynamic value?

[richard-powerbi]

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?

[Smauro · Accepted solution]

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"

[richard-powerbi]

@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!