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

[Txtcher]

I am currently building a Power Bi report that tracks investigations. I would describe my skill level as an intermediate beginner with Dax, and an advanced user with Power Query. Here is the challenge:

- Each week an Excel file is posted to a SharePoint document library. It is stored in the corresponding FY folder and has a naming convention of 01. Filename, 02. Filename, etc.

- This Excel file contains a table and a pivot table.

- Staff then cut/paste the pvt counts into another Excel file (that I do not have access to) to create a history of the counts.

The challenge is to get the weekly counts into Power Bi.

This has me so muddled I did not even know where to post this question. I know I can grab the latest file easy enough using power query, but then how do I get the row counts and store them into a history table?

[Txtcher]

Thank you so much for your response. I can't believe I didn't think to use grouping. 😑

[MasonMA · Accepted solution]

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:)

[Txtcher]

@MasonMA 

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
    ExpandedData

[MasonMA]

If 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)

[danextian]

Hi @Txtcher 

If you want to get the weekly count, connect to all the files in the folder instead of the latest as every refresh replaces the previous data and no history is kept. Another option is to use Power Automate a few minutes after the semantic model refresh to export the current count to a SharePoint folder as a file. But even with this approach, Power BI still needs to connect to all the exported files in the folder so the counts are aggregated and history is preserved.