How to consolidate multiple Excel files with multiple sheets - Easy and Tricky examples скачать в хорошем качестве

How to consolidate multiple Excel files with multiple sheets - Easy and Tricky examples

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    • The best way to connect to a SharePoint Fo...   Consolidating files with different headings    • How to use Power Query to Combine Multiple...   📂Source Files I used can be downloaded from here: https://aasolutions.sharepoint.com/:f... 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