by Wyn Hopkins

Consolidate multiple Excel worksheets using Power Query

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.

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)