Download Excel Start File: https://excelisfun.net/files/EMT1626-Start.xlsx
Download Excel Finished File: https://excelisfun.net/files/EMT1626-Finished.xlsm
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