Combine Excel Worksheet Data (Not Excel Tables) Into PivotTable using Power Query (EMT 1626)

Download Excel Start File:

Download Excel Finished File:
Learn all about


1. (00:01) Introduction

2. (00:25) 3 Problems with Consolidating Worksheet Data (Proper Data Sets)

3. (01:18) Structure of Workbook

4. (01:33) Create Worksheet Formula to Dynamically Get File Path

5. (03:23) Use Defined Names to bring File Path into Power Query

6. (04:00) Power Query Two Way Lookup to get File Path.

7. (04:47) File.Contents Power Query Function to access Excel Workbook File and all the Objects in the Excel File.

8. (05:47) Save File Path Query as Connection Only

9. (05:44) Reference File Path Query

10. (06:00) Excel.Workbook Power Query Function to pull Excel Objects from inside Excel File into the Power Query Window.

11. (06:29) Promote Headers from Worksheet with Excel.Workbook functions second argument with true

12. (07:04) Filter Excel Objects: Sheets and Not Sheet with Name “Report”

13. (07:42) Add data in Worksheet to see if Query Updates.

14. (08:15) When we use Excel.Workbook in a file to get the objects in the same file, we have to Save and Refresh to get Query to Update.

15. (08:41) Delete data in rows of Worksheet and look at problem that it causes the query.

16. (09:02) Add extra data to side of Proper Data Set in Worksheet and look at problem that it causes the query.

17. (09:28) Add Custom Column to Prevent Unwanted Columns from ending up in our Append (Combined) Table.

18. (10:06) Table.ColumnNames and Table.AddColumn Power Query Functions.

19. (10:48) Custom Functions: Building and Understanding Power Query Custom Functions. Why we might want to Define Custom Functions rather than use the each keyword.

20. (12:19) List.Select and Text.StartsWith Power Query Functions.

21. (13:43) NOT operator in Power Query: not.

22. (14:10) Table.SelectColumns Power Query Function.

23. (15:00) Summary about why we might want to Define Custom Functions rather than use the each keyword.

24. (15:24) Table.Combine Power Query Function.

25. (16:16) Remove Blank Rows.

26. (16:30) Load Appended Table to PivotTable Cache.

27. (16:49) Test by adding new data.

28. (17:06) Create Macro to Automate Save and Refresh.

29. (17:50) Summary

Excel,Highline College,Mike Girvin,excelisfun,excel is fun,EMT,EMT 1622,Excel Magic Trick 1622,Table.SelectColumns,List.Select,Table.ColumnNames,Text.StartsWith,Table.AddColumn,Excel.Workbook,File.Contents,Power Query Custom Function,Understanding Power Query Custom Functions,Table.Combine,EMT 1626,Excel Magic Trick 1626,Consolidate Sheets in Excel,Append Sheets in Excel,Combine Sheets in Excel,