topic Re: Parameter from dynamic value? in Power Query

Parameter from dynamic value?

richard-powerbi — Fri, 19 Jun 2020 15:04:45 GMT

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?

Re: Parameter from dynamic value?

Smauro — Fri, 19 Jun 2020 15:36:55 GMT

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"

Re: Parameter from dynamic value?

richard-powerbi — Fri, 19 Jun 2020 16:38:28 GMT

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!