Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
khift363
Frequent Visitor

Dataflow Incremental Refresh on integer datetime, no GUI option available

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.

khift363_1-1679414638293.png

 

 

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.

khift363_0-1679414541549.png

 

 

 

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.

1 ACCEPTED SOLUTION
jkz17
Regular Visitor

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

View solution in original post

3 REPLIES 3
jkz17
Regular Visitor

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

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
Super User
Super User

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.