digest
Short Summary
Main recommendation: Use Power Query's "From Folder" connector to import every Excel workbook in a folder into one combined table, then build a pivot table on top so that adding new files and refreshing updates everything automatically. The video demonstrates this with three Excel files, then adds two more (San Francisco and Tacoma) to show the refresh working.
Key Points
- The method relies on Power Query's Excel.Workbook function, which extracts the sheet data from the binary Content column of the folder listing.
- Headers are promoted with Table.PromoteHeaders, because the imported data initially places column headers in the first row.
- Removing the "Use Original Column Name as Prefix" option gives clean field names instead of prefixed headers after expansion.
- The pivot table is built from the loaded query table, so refreshing the query and then the pivot table picks up newly added files.
Details Worth Noting
- Concrete instructions:
- Go to Data > Get Data (Power Query) > From File > From Folder, browse to the folder with the Excel files, then click OK twice.
- Rename the query (the video uses "Import Excel Files") and right-click the Content column, then choose Remove Other Columns.
- Add a custom column named "Get Excel Data" with the formula: Excel.Workbook([Content]).
- Remove the Content column, then expand the Get Excel Data column using the side-to-side arrow.
- Add a second custom column named "No Headers" with the formula: Table.PromoteHeaders(Get Excel Data.Data), then remove all other columns.
- Expand the "No Headers" column and uncheck "Use Original Column Name as Prefix" to get the real field names as headers.
- Load the query to a new sheet as a table (Home > Load To).
- Fix data types as needed: the video sets Date as Date, Sales as decimal, and Store ID as Text.
- Create a pivot table with Sales in Values, Store ID in Rows, currency number formatting, Outline report layout, and a City slicer.
- To update, add new Excel files to the folder, then go to Data > Refresh All and right-click the pivot table to refresh it.
- The presenter credits Miguel Escobar for the header-promotion and prefix tips, which he says he learned from Miguel's blog/YouTube channel.
- The presenter notes that Power Query works in Excel 2010 or 2013, and that the prefix-removal option came in a recent November update he had just gotten.
- Some date and number fields may not import with the correct type automatically, so check data types after loading.
Bottom Line
Once the query is built, the folder becomes a live data source: drop in new workbooks, refresh, and the table and pivot table update without rebuilding anything.
