Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Incremental refresh using datetime

Hello   we have a dataset whereby the rows contained a datetime field. But because of the high cardinality we splitted this in a date and a time field. We have to refresh this data on a daily bas...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    Yes. That was your goal, to keep the cardinality low, right?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

lbendlin one more question. It is not possible to use just a Date field (so not a Datetime field) for RangeStart and RangeEnd?

regards
Ron

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Sure. All you need is to cast your date field as datetime during the comparison  (basically pegging the time at midnight)

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hmm, how does the casting work lbendlin  ?
      Not yet so experienced in Power Query and refresh

      • lbendlin's avatar
        lbendlin
        Super User
        4 years ago

        Like so:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU31DcyMDJUitUBcwyQeUYwXiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each DateTime.From([Column1]) > RangeStart and DateTime.From([Column1]) <= RangeEnd)
in
    #"Filtered Rows"

         

        How to use this code: Create a new Blank Query, then click on "Advanced Editor", and then replace the code in the window with the code provided here. Then click "Done".