Forum Discussion
Incremental refresh and historic data from multiple sources
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.
5 Replies
- AmiraBedh
Super User
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.
rohit1991Hi mch98
Mixing a "cold" archive (weekly files) with a "hot" daily feed is common, and Incremental Refresh will behave only if you set it up on one foldable query.
Here are the steps:
1. Stage the two sources
-
HistoricFiles -folder of weekly CSV/XLS/etc. (disable load)
-
CurrentFeed -SQL view/table (disable load)
Make their schemas match (same columns, data types, names).
2. Create the final query by referencing, not duplicating
let Historic = HistoricFiles, // already cleaned Current = CurrentFeed, // already cleaned Combined = Table.Combine({Historic, Current}), // RangeStart / RangeEnd are DateTime parameters you created Filtered = Table.SelectRows(Combined, each [YourDateColumn] >= RangeStart and [YourDateColumn] < RangeEnd) in FilteredOnly this Combined query is loaded. Put Incremental Refresh on it.
3. Order of steps matters
The date filter (using RangeStart/RangeEnd) must be the last step before the output. Anything after that can break folding and kill IR.4. First publish trick
If the historic chunk is huge, publish once without IR, run one full refresh, then enable IR and republish. That keeps the first policy refresh small.-
