cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alex-syk
Frequent Visitor

Dataflow Incremental Refresh - Problems with Excel

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: 

Capture-0.PNG

2. I connect to this datasource with a dataflow:

Capture-1.PNG

 

3. I then configure the incremental refresh:

Capture-2.PNG

 

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:

Capture-3.PNG

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

Capture-4.PNG

#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):

Capture-5.PNG

 

6. I refresh the dataflow

 

7. I view the data in the dataflow:

Capture-6.PNG

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.

2 REPLIES 2
v-xiaosun-msft
Community Support
Community Support

Hi @alex-syk ,

 

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.

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors