Dataflow Incremental Refresh - Problems with Excel

[alex-syk]

I'm currently testing the incremental refresh feature with a dataflow. The dataflow is not complying with the incremental refresh settings and I do not understand why. Below is a minimal working example to reproduce my problem.

1. The data source is a simple Excel workbook with 13 rows of data: 

2. I connect to this datasource with a dataflow:

3. I then configure the incremental refresh:

4. At this point, if I view the data in my dataflow, I see that RangeStart and RangeEnd have been created, but they are the earliest and latest possible dates, respectively:

#datetime(0001,01,01,00,00,00) meta [IsParameterQuery=true, List={}, Type="DateTime", IsParameterQueryRequired=true, Description="autogenerated_for_incremental_refresh"]

#datetime(9999,12,31,23,59,59) meta [IsParameterQuery=true, List={}, Type="DateTime", IsParameterQueryRequired=true, Description="autogenerated_for_incremental_refresh"]

Even though RangeStart and RangeEnd look incorrect, I'll continue with my example for the sake of completeness.

5. I then modify the Excel workbook datasource (the "Data" column is "MM" instead of "M" on 1/1/2022 because I want to check that this row doesn't get refreshed, which it shouldn't based on the incremental refresh settings):

6. I refresh the dataflow

7. I view the data in the dataflow:

As you can see, the dataflow did not follow the incremental refresh settings (step 3 above). For example, the "Data" column should still be "M" on 1/1/2022).

What is happening? As noted above, I'm sure this has to do with RangeStart and RangeEnd being incorrect. But why do they not correspond with the dates in the incremental refresh settings? Also, I just tried this with a Parquet file in Azure Blob Storage, and the same problem occurs.

Finally, I asked a similar question while testing the incremental refresh of a datamart. It seems Microsoft fixed that issue because I can successfully implement an incremental refresh using my above test data with a datamart. I don't know why a dataflow would be different in that regard.

Also, this question is cross referenced in "Forums > Get Help with Power BI > Service" since I originally posted the question there but I think it is more appropriate to post here. 

[v-xiaoyan-msft]

Hi @alex-syk ,

Even though RangeStart and RangeEnd look incorrect ....

My confusion is why do you keep using this even though you know it's not quite right?

Best regards.
Community Support Team_ Caitlyn

[alex-syk]

Hi @v-xiaoyan-msft, thanks for the response!

Given my incremental refresh settings (step 3 in my original post), "Store rows from the past 20 years. Refresh rows from the past 7 days", what should RangeStart and RangeEnd be? They get used in this automatically generated applied step:

 

Table.SelectRows(#"Changed column type", each DateTime.From([DateTime]) >= RangeStart and DateTime.From([DateTime]) < RangeEnd)

I can see how that step takes into account the "Store rows from the past ..." but not the "Refresh rows from the past ..."

Also, isn't this a bug? If I enter values into the incremental refresh settings window (step 3 in my original post), then they should be applied correctly to the data. Plain and simple. I shouldn't need to check or edit RangeStart and RangeEnd to make sure that they correspond with what I entered in the incremental refresh settings window. Otherwise, what's the point of the incremental refresh settings window?

Looking forward to your reply, and thank you for your help!