Dataflows incremental refresh

[CrazyHorse66]

Hi All

I have been looking at this for far too long now, any help gratefully received.

1. Created a simple dataflow in a Premium Workspace - connect to SQL DWH and pull all data (it's a very small dataset to test with, 102 rows only - no where clauses just select fields a,b,c,d from table z)

2. Set up Incremental Refresh using CreatedDate - to store rows from the past 1 month, refresh from the past 7 days - should be 26 rows only - against a DateTime field

3. Refreshes the dataset but I still have 102 rows

4. The RangeStart value has been created but not accounting for the refresh schedule:

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

5. The RangeEnd value has been created but not accounting for the refresh schedule:

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

What am I doing wrong? Why is the data not being reduced?

Thanks

[v-jayw-msft]

Hi @CrazyHorse66 ,

Try applying a filter on the Created Date column and check the incremental refresh again.

Best Regards,

Jay

[CrazyHorse66]

Update: I started all over again and as suggested needed to rerun the refresh 2 or 3 times to take effect - thanks

[lbendlin]

Can you confirm that your sample's [Created Date]  values fall into the buckets defined by the partitions?

[CrazyHorse66]

Hi, here's the 'raw' data, dates in September 2021

And here's what I set for incremental to test, but nothing changes

Strange though it has the values twice in dropdown to filter - Created Date

[lbendlin]

That would be your answer.  Your source data is ambiguous with two columns having the same name, and the Power BI service got confused and chose the wrong one.

[CrazyHorse66]

Thanks, but the source data does not have columns with same name, for some reason the dropdown to select from does however?

[lbendlin]

If you have a Pro license you can consider raising a Pro ticket at https://powerbi.microsoft.com/en-us/support/pro/

[lbendlin · Accepted solution]

3. did you mean to say "refreshed the dataflow" ?

How many times did you refresh the dataflow?  Incremental refresh only kicks in at the second refresh.

How many partitions are reported in the refresh history log file?

[CrazyHorse66]

Hi and thanks

Yes I meant it successfully refreshes but the data does not get filtered

I've tried multiple refresh and the data stays the same, RangeStart & RangeEnd do not have the dates I have selected for Incremental Refresh

It looks like there are 7 partitions but none are being processed?

[lbendlin]

Please post the last line of the power query.

[CrazyHorse66]

#"Fact_ChatTransfer-4372656174656444617465-autogenerated_for_incremental_refresh" = Table.SelectRows(Source, each DateTime.From([CreatedDate]) >= RangeStart and DateTime.From([CreatedDate]) < RangeEnd)

in

  #"Fact_ChatTransfer-4372656174656444617465-autogenerated_for_incremental_refresh"