Consolidate Multiple Excel Worksheets using Power Query Custom Functions

A common request on forums and in the training classes we run is "how do I consolidated multiple sheets into a summary?"

Power Query can do it, I step you through how I would approach it.
Techniques covered include
- Creating a custom function (via the User Interface)
- Unpivotting
- Load destinations (sheet v data model)

Tip: Make sure your Power Query formula bar is on via the View menu in the Power Query Editor.


Link to a demo file and the solution: https://aasolutions.sharepoint.com/:f...

Note: This process can be easier when you are consolidating sheets from an external workbook (you can actually get a list of the sheet, table and range names rather than having to manually create one). So if you’re looking to consolidate multiple sheets from a different Excel file then check out the 5 minute mark of this video    • Power BI Hidden Gems  

I’m using Power Query in Power BI but the technique is the same for Power Query in Excel




Check out our blog: www.accessanaltyic.com.au/blog