Incremental refresh and historic data from multiple sources

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.