Dataflow Incremental Refresh - Problems with Excel

[Anonymous]

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.

[v-xiaosun-msft]

Hi @Anonymous ,

After set up the system generates an automated range start resp. range end in the dataflow. This is the problem. 

The problem is linked with RangeStart & RangeEnd, 0001.1.1 00:00:00 is not the correct data format.

You can convert it to something like December 30th, 1899, which is the base date for DAX. Or you can set the range manually in the dataflow with "normal" dates dataflow.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your reply, @v-xiaosun-msft!

I don't understand what you are suggesting. RangeStart and RangeEnd have the correct datatype: DateTime. However, the values of RangeStart and RangeEnd are obviously wrong since they don't correspond to what I entered in the incremental refresh settings (step 3 in my original post). This leads to two questions:

1. What should the values of RangeStart and RangeEnd be so that they correspond with the incremental refresh settings from step 3 (both "Store rows from the past" and "Refresh rows from the past")?
2. Is this going to get passed along to an engineer that can fix this internally? This bug should be fixed as soon as possible