Forum Discussion

richard-powerbi's avatar
richard-powerbi
Post Patron
6 years ago
Solved

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?

Need Help
  • Smauro's avatar
    Smauro
    6 years ago

    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"


2 Replies

  • Smauro's avatar
    Smauro
    Solution Sage
    6 years ago

    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"