Incremental refresh using datetime

[PowerRon]

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 basis. But for that we need a datetime field.
But then we create again the isssue of the high cardinality.

In the table we also have a day-key.

Do you always need a datetime field to do a refresh?
How to solve this problem?

Regards

Ron

[PowerRon]

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

[PowerRon]

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

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

[PowerRon]

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

[lbendlin]

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".

[PowerRon]

great, thnx

[lbendlin]

Your RangeStart and RangeEnd parameters need to be DateTime, and they need to be used in the Power Query filter for the query/table that you want to implement incremental refresh for. 

But it doesn't really matter what they compare to.  So you could write a filter that says

DateValue+TimeValue > RangeStart and DateValue+TimeValue <= RangeEnd

[PowerRon]

Ok, so you then keep the date and time field as separate fields? Also when comparing to RangeStart and RangeEnd>

[lbendlin · Accepted solution]

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