Power BI Incremental Refresh

[Anonymous]

Hi.

I have souce from on prem sql and source from excel file in sharepoint and when publishing to service gateway is approved for SQL.

I also have an MS Flow that refreshes power bi dataset every 5minutes. My problem is  2 fold :

1. Incremental refresh -Issues around query folding

2. AI text Analytics reruns for entire dataset every time refresh is done.

How can I refresh only what was updated and AI text analytics only work with new rows added to the excel file and not the entire dataset? Based on my current set up I need a solution please or guidance towards one

[mwegener]

Hi @Anonymous,

can you identify the added rows by a date column in Excel?

Take a look at this too.

https://www.thinkbi.de/2020/03/29/incremental-refresh-with-subqueries/

[Anonymous]

Hi

Yes, however can this be applied to an sharepoint excel file ? Will query folding be an issue?

Thanks

[mwegener]

Hi,

you can apply the filter, it just won't be query folded to the source.

You do not get any speed advantage, but you can reduce the calls to AI text analytics to the new rows.

[Anonymous]

Hi

Thank you, will this be incremental refresh or power query setting on filtering the rows? and if I can dynamically pull the rows will historic data remain and only latest gets refresh or new rows added? 

Thanks

[mwegener]

Hi @Anonymous,

in my view, it's an incremental refresh that you need to set up through Power Query Filter.

The historical data remain and only the defined refresh range is updated.

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh

I wrote a blog post on a similar topic.

https://community.powerbi.com/t5/Community-Blog/Incremental-Refresh-with-Subqueries/ba-p/1003337