Dataflow Incremental Refresh on integer datetime, no GUI option available

[khift363]

I am attempting to set up a dataflow from a source that does not have native datetime values -- instead, the data sources have integer values for date and time. I followed the guide at ( https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure#convert-dateti... ) and converted the RangeStart and RangeEnd values to integer values and then set up filtering based on those values.

The filtering works exactly as the guide says -- changing RangeStart and RangeEnd manipulates the native query passed to the source and correctly filters the rows exactly as I want them to be filtered.

The dataflow GUI, however, will not allow incremental refresh to be instated, instead demanding a datetime column to filter on. I cannot create a datetime column and still have the query fold -- every attempt I've made to turn to the integer date and time values into a datetime has resulted in a non-foldable query, even though I can trivially write native SQL that can accomplish that within the source server.

I cannot get Power Query to convert the integer values to datetime values and still meet the prerequisites for incremental refresh (query folding). It would be trivial for me to write a native query that can accomplish this. Realistically speaking, what kind of performance hit would be incurred if:

1. I wrote a native query that hashed the integer date and time values into a datetime then passed that query to M, force enabled native query folding, and did the rest of the work in M?
2. Or, what if I just wrote the entire query in native SQL and passed that to M with native query folding enabled?

Frankly, working with M is so much slower and clunkier than native SQL that I'm spending 10x as much time developing this as I would if I were writing standard SQL and I'm not even sure if it's accomplishing anything. If option 2 has little to no performance implications then that would far and away be my preferred way to develop this application. Maybe I could see it mattering if this final report / output were querying the source through Direct Query because it could use metadata to enhance some things, but for this situation I'm trying to automatically import all data as it comes through and do the rest of the calculation inside of a datamart so I don't see the value in maintaining any metadata within the process.

[jkz17 · Accepted solution]

I was able to get around this issue. 

1) Load your data into the dataflow

2) Create a dummy datetime column. This will break query folding for now but that's okay

3) Save and close the dataflow. Set up your incremental refresh policy using the dummy. 

4) Go back into the dataflow. Delete your dummy variable and set up the incremental refresh with the integer column. Query folding should start working again

[khift363]

This is actually what I was working on, with a couple extra steps necessary to get it all flowing right. Namely, don't delete the dummy column -- instead, after enabling incremental refresh go back and redefine the dummy column to be 1 minute ahead of RangeStart. This will then fold, and will partition correctly as well, as the incremental refresh feature does use the datetime value to determine partitions I believe.

[lbendlin]

Whatever works for  you will be fine.

You are missing the point of a dataflow slighty though. The point of a dataflow is to shield you (the developer) from a slow data source.  Your SQL server is likely not slow.  You would be better off using datasets.