Excel Files on SharePoint - New File Each Week - How to Store Pivot Counts For Ea Week?

Hello Txtcher 

You may follow below high-level steps,

1. Connect Power BI to the SharePoint folder and it gives you a table of all files in the document library.

2. Filter for the investigation folder (FY25, FY26, etc.) and filter filenames for the naming convention (e.g., "Filename.xlsx").

3. Extract the Excel Table data by selecting the investigation table, not the pivot. Avoid relying on pivot tables since they can break/change.

4. From the filename, extract the prefix (01., 02., 03. …) and convert that into a "Week Number" column. You can also add a column from file properties (Date Created) to track upload week.

5. Combine all files into one fact table and append all weekly tables into one history table with columns.

6. Aggregate row counts in DAX or Power Query. In Power Query you can use 'groupby' Week and count rows.

Or in Power BI with DAX like Weekly Count = COUNTROWS('Investigations')

Then create visuals that show trend of investigation counts over time.

There are also a few tutorial videos on youtube that would work in your case and i'd recommend following the steps demoed in these videos.

1. Combine Data from Multiple Excel Files | Most Dynamic Method (Dynamic Columns & Sheets)

The video demos with Local folder but the logic also applies on Sharepoint folder. 

2. How to combine (and debug) Excel files From SharePoint Folder

Hope this helps:)