Values change only after adding ModificationDate column (Incremental Refresh)

[muskagap]

Hi,

I use Incremental Refresh in my PBI report. SQL table is my data source. There's an extra ModificationDate column on that table to indicate which rows have change over last 3 months. 

Now, I go to PBI Service and refresh semantic model. I have a table in my report where I can not see changes made on SQL table. Seems Incremental Refresh doesn't work. However, when I add ModificationDate column to my report's table then I can see all changes. The other issue is that changes appear when I press F5 and realod the report.

I don't understand why. Manual semantic model's refresh doesn't refresh report as well? The question is whether automatic refresh will show the latest data in report?

PS. I have 'Detect data changes' option checked in Incremental Refresh setting (on ModificationDate column).

[Anonymous]

Hi pallavi_r ,thanks for the quick reply, I'll add more.

Hi @muskagap ,

Regarding your question:

when I add ModificationDate column to my report's table then I can see all changes.

I think the refresh has been successful at this point, and the "ModificationDate" at this point serves as the primary key to display the newly added data. I don't know which fields are used in your table visual, but Power BI will not display duplicate data unless you add a primary key column.

The other issue is that changes appear when I press F5 and realod the report.

There may be a delay of several minutes after the refresh is successful (depending on the amount of data you have)

Clicking this button will refresh the data cached in the visual .

Best Regards,
Wenbin Zhou

[pallavi_r]

Hi @muskagap ,

Can you please ensure the below 2 items are taken care for incremental refresh to work effectively.

i)  the data parameter which is used for filteration of data model using rangeStart and rangeEnd should be different from column used in Data Detect Changes.

I was facing this issue when I was using the same date column for filtering rangestart, rangeend and detect data changes.

ii)Ensure query folding is happening by right clicking power query step and check for view native query enabled. Sometimes due to data type mismatch from source and destination, query folding does not work.

More details @ https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-troubleshoot

Thanks,

Pallavi

[muskagap]

Hi @pallavi_r ,

thank you for your reply. Actually, for your both questions the answer is yes. Data column is different and query folding works. I've read that detecting works on automatic refresh, however it may not work if your refresh data manually. F5 come sin handy then. In real scenario I would be using automatic refresh instead of manual one so most likely my problem will vanish, however I was just curious about manual refresh.