Forum Discussion
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:)
I am using the steps provided in the first video link you provided (btw, I am a big fan of Goodly and have his book).
This approach combines all the data into a single table very efficiently and quickly. Below is the m-code for my query. My only hitch will be when they create a new folder for each FY. I don't know how I will handle that dynamically. The only method I can see is to create a new query for the new yearly folder and append that data.
let
Source = SharePoint.Files("xxxxxxxxxxxxxxx”),
FilterFolderPath = Table.SelectRows(Source, each Text.Contains([Folder Path], "Backlog Summary")),
TrsfrmContentExcelWkbk = Table.TransformColumns(
FilterFolderPath, {"Content", each Excel.Workbook(_)}
),
ExpandedContent = Table.ExpandTableColumn(TrsfrmContentExcelWkbk, "Content", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
// Table 2 contains the case listing
FilterTable2 = Table.SelectRows(ExpandedContent, each ([Name.1] = "Table2")),
AddWeekNo = Table.AddColumn(FilterTable2, "Week No", each Text.Start([Name], 2)),
RemovedOtherColumns = Table.SelectColumns(AddWeekNo,{"Data", "Date created", "Week No"}),
ExtractedCreateDate = Table.TransformColumns(RemovedOtherColumns,{{"Date created", DateTime.Date, type date}}),
ExpandedData = Table.ExpandTableColumn(ExtractedCreateDate, "Data", Table.ColumnNames(ExtractedCreateDate[Data]{0}) )
in
ExpandedDataIf your files are named 01. Filename.xlsx, 02. Filename.xlsx, … this extracts the number before the dot, becomes WeekID. For the M code, it would be something like
#"AddedWeek" = Table.AddColumn(#"Filtered Rows1", "WeekID", each Text.BeforeDelimiter([Name], "."), type text),
#"ExpandedData" = Table.ExpandTableColumn(#"Added Week", "Data", {"Col1","Col2","Col3"})
added to your M after your '#"Filtered Rows1" ' step (Please adjust those 'Col1...' names)