Incremental Refresh of on prem excel data source

[mp390988]

Hi,

I have an excel file that is saved in a directory.

My power bi report consumes data from this excel file. 

The excel file gets replaced with new data which causes my report to lose historial data. 

What I need is an incremental refresh so that historical records are not wiped out.

How do I do that? Is it possible to set up incremental refresh for an on prem excel data source?

Thank You

[V-yubandi-msft]

Hi @mp390988 ,

If you’ve had a chance to review my previous response and it addresses your issue, please consider marking it as the accepted solution this helps other community members who may face a similar challenge find answers more easily.

Thank You.

[V-yubandi-msft]

Hi @mp390988 ,

Thanks for being part of the Fabric community.  @pankajnamekar25 ,  highlighted an important point, and I’d like to build on that.

Power BI officially supports incremental refresh only when the data source allows query folding, as outlined in Microsoft documentation.

FYI:

Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn

Since Excel files especially on premises do not support query folding, they aren’t directly compatible with incremental refresh.

Alternative Approach

You can work around this limitation by using a self referencing Power BI Dataflow, which allows you to

1. Retain historical records from previous refreshes 

2.  Append only new data from the updated Excel file

3.  Maintain a cumulative dataset without overwriting existing data.

Thank you for your quick response @pankajnamekar25 .

Regards,

Yugandhar.

[mp390988]

Hi @V-yubandi-msft ,

I did not know with Dataflows historical records can be maintained. 

I thought Dataflows is just the same as Query Editor but just an online version which is savable so that other developers can use it for their own reporting without having the need to do the whole process of data cleasning.

I wasn't aware that there are more benefits to using a Dataflow like the ones you have mentioned i.e. append only new data and maintain historial records because even normal Power Query Editor doesn't offer these advantages right?

Is there an option in Dataflows or a setting that tells you "Only append new data"? Do you have a screenshot or something you can provide please? Also, what licence I need for creating dataflows and which role permissions do I need to do this (i.e.contributer, member?)

Thank You,

[V-yubandi-msft · Accepted solution]

Hi @mp390988 ,

You're  right, and I appreciate your patience. I realize my previous explanation might not have fully clarified the details, so I apologize for any confusion.

Regarding the  Append Only  functionality in Dataflows, there isn’t a direct toggle for it, and I misspoke on that point. That was my mistake.

For Dataflows refresh settings, I’ve attached the most recent official documentation for reference: Incremental refresh in Dataflow Gen2 - Microsoft Fabric | Microsoft Learn

As for licensing, managing Dataflows requires a Power BI Pro or Premium Per User  license. Additionally, you’ll need either a Contributor or Member role to configure and edit them.

Hopefully, it provides a clearer picture.

Thank You.

[pankajnamekar25]

Not directly.
Power BI incremental refresh officially supports:

SQL databases (SQL Server, Azure SQL, etc.)

Dataflows

Some cloud sources like SharePoint and OData

Excel files (especially on-prem) are not supported directly for incremental refresh

Use Power BI Dataflows or Dataverse

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.