Power Query Data Consolidation
Budget: $50 – $0 CAD
I have 300 weekly Excel files, each following the same layout, and I need their key metrics rolled into one master workbook. I’m running the latest Excel from Office 2024, so Power Query is available and should be the ideal tool for this.
What I want: a single, refreshable table that automatically pulls ten fields from every weekly file—dates, ROR, fund balances, calculated withdrawal amounts, and six other columns already labelled the same way across all workbooks. All 300 files sit in one folder; more will be added over time, so the solution has to cope with new files without extra setup.
Please configure Power Query to:
• Connect to that folder
• Extract the ten columns in their native data types
• Load everything into one structured table I can sort, filter, and chart immediately
The end result must work on my machine in Edmonton, be easy to refresh with a single click, and require no VBA. If any transformations are needed—text-to-number fixes, date parsing, column renaming—build them into the query so the output is clean every time.
Once done, send me the finished workbook plus a brief note explaining where to drop new weekly files and how to refresh the data.
What I want: a single, refreshable table that automatically pulls ten fields from every weekly file—dates, ROR, fund balances, calculated withdrawal amounts, and six other columns already labelled the same way across all workbooks. All 300 files sit in one folder; more will be added over time, so the solution has to cope with new files without extra setup.
Please configure Power Query to:
• Connect to that folder
• Extract the ten columns in their native data types
• Load everything into one structured table I can sort, filter, and chart immediately
The end result must work on my machine in Edmonton, be easy to refresh with a single click, and require no VBA. If any transformations are needed—text-to-number fixes, date parsing, column renaming—build them into the query so the output is clean every time.
Once done, send me the finished workbook plus a brief note explaining where to drop new weekly files and how to refresh the data.