In these 2 videos (combined into 1), you’ll learn how to consolidate multiple Excel files with ease, whether they’re neatly organized or full of messy data. I’ll demonstrate two powerful methods using Power Query—one for clean datasets and another for files that require cleanup. By the end, you’ll be able to combine annual Excel files with multiple monthly sheets, remove unwanted headings, unpivot columns, and automate the entire process using a custom function. Whether you’re working with simple folders or trickier setups, this tutorial will help you efficiently gather, clean, and load your data into a single, streamlined table.
Plus, I’ll share some helpful Power Query tips along the way, including using filters, custom functions, and smart tricks like promoting headers automatically. Perfect for Excel users looking to save time and reduce manual work! Stick around until the end and see how to clean messy datasets and learn advanced tricks to automate everything!
📺Videos Mentioned:
SharePoint Folder consolidation method
The best way to connect to a SharePoint Folder to speed up your Excel and Power BI data refresh
https://youtu.be/-XE7HEZbQiY
Consolidating files with different headings
https://youtu.be/09tvia_8ykI
📂Source Files I used can be downloaded from here:
https://aasolutions.sharepoint.com/:f:/s/PubliclyAvailableContent/EnmZddyssRhLhDs7jGVwWkgBbl46Q5wbjJKojOpMXQy-hw?e=CSW9rv
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/
Did you know I've written a book "Power BI for the Excel Analyst"?
📖https://pbi.guide/book/
Connect with me
https://wyn.bio.link/
00:00 Introduction
00:03 Two Methods for Excel File Consolidation Explained
01:03 Setting Up Data for Power Query
02:00 Importing Data from a Folder into Power Query
04:06 Expanding Sheets and Filtering Unwanted Data
06:01 Handling Column Headers and Creating a Date Column
10:15 Dealing with Tricky Data (Unwanted Headings & Bad Layout)
13:03 Building a Custom Function to Clean Up Data
18:24 Final Steps: Loading Consolidated Data into Excel
19:16 Closing Remarks and Power Query Playlist Suggestion