Incremental refresh and historic data from multiple sources

[mch98]

Hello everyone,

i'm working on the follwoing scenario:

the overall goal ist track progress across multiple topics via excel workplans on a weekly basis. One important visual is the change of count of status (e.g. completed, ongoing, overdue) over time. I use the calendar weeks for x axis and count of 'status'. The calendar week is retrived though the submission date of the excel file. Now to the problem: I have a total of six input excel files, they have identical columns and are easy to append. Two files have 4 or more sheets that need to be added  and all data is stored on a sharepoint. For the most current week or the ltest update i managed to connect everything, but when it comes to appending data in a historic file so the current week is added, i have difficulties. So far my input data is transformed by a query (to combine the separate sheets) into a seperate input excel . Through PowerBi i grab those six input excels and appends them into a new file. I tried using incremental refresh to keep the data load over time to a minimum but that did not work as i have that curated excel in between.

Does anybody have a fix or has been in similar situation. Ideally of course i want to be able to drop the current weeks 6 excels in a dedicated folder and the historic data is appended automatically for the rampdown visual. 
Looking forward to hearing your ideas and thank you.

[v-sdhruv]

Hi @mch98 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @mch98 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[rohit1991]

HI @mch98 ,

Your scenario is quite common when tracking progress over time with multiple Excel files as sources, especially when dealing with weekly updates and wanting to automate historical data accumulation. The challenge you’re facing is that incremental refresh in Power BI doesn't work well with pre-aggregated or transformed data stored in intermediate Excel files — particularly when you manually curate or append them before loading into Power BI.

To achieve the automation and scalability you're looking for, consider restructuring your approach slightly. Instead of using a curated Excel file as an intermediate, set up Power BI to directly query the SharePoint folder where all weekly files are stored. Power BI can automatically combine Excel files from a folder, especially if they follow a consistent structure. Use Power Query to filter sheets and transform data as needed during the load process. This removes the need to manually merge sheets beforehand and enables Power BI to handle it dynamically each time a new file is dropped into the folder.

For tracking changes over time, include a submission or extraction date column (like the file creation or modified date) in your Power Query steps so each record is timestamped. While you may not be able to use Power BI’s built-in incremental refresh directly on Excel files, you can mimic its behavior by keeping the dataset in import mode and loading only relevant files (e.g., filter by recent dates during preview).

For more robustness long-term, consider using a simple staging area in a database or SharePoint list that accumulates and preserves history, and let Power BI pull from there. That way, you're not limited by the refresh capabilities of Excel files and can manage large historical volumes more efficiently.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar

[AmiraBedh]

On SharePoint:
- Create a Weekly Uploads folder.
- Every Monday or whatever day you collect the weekly reports, drop the 6 Excel files for that week into this folder.
- Rename each file with a timestamp or week number Workplan_TopicA_2025W12.xlsx.

Instead of pointing to a single file, use SharePoint Folder connector (or local Folder connector if syncing locally).
Power Query will read all files in the folder and let you filter/transform.

You can extract a submission date from the file name or file metadata (like Date created) and use that as your Calendar Week.

You’ll need to:
- Loop through each Excel file.
- In Power Query:
- Expand each file's content.
- If the file has multiple sheets, loop through each sheet.
- Append all into a unified table.
- Add a [SourceFile] column and extract the week or submission date from it.

This can be done with:

= Table.AddColumn(PreviousStep, "Week", each Date.WeekOfYear([SubmissionDate]))

Or if pulling week from filename:

= Text.Middle([Source.Name], 20, 6)

Since incremental refresh doesn’t work on Excel files well, use manual refresh logic. Your Power BI model is now reading from the folder. So every time you drop new weekly files, Power BI appends them during the next refresh.