Download Excel Start File: https://excelisfun.net/files/EMT1660Start.xlsx
Download Text Files: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/ExcelFiles.zip
Download Excel Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1660Finished.xlsx

This video teaches how to remove all blank rows and columns in all Excel files during import using Power Query Custom Functions. See Two Methods for build Custom Functions to complete this task. These functions Streamline and Make Efficient this usually arduous task.
Topics:
1. (00:01) Introduction and Overview of entire data import and transform process.
2. (00:33) Jim’s Question.
3. (00:48) Look at Files that must be imported.
4. (01:23) Power Query to avoid manual tasks.
5. (01:47) Import Initial Excel Files using From Folder.
6. (02:26) Prepare File Name Column so we can use City Name in Final Table.
7. (02:23) Excel.Workbook Function to extract Objects from Excel File.
8. (03:20) Second argument in Excel.Workbook Function to promote Headers (Field Names). For example #1, we do NOT Promote Headers.
9. (03:35) Look at Table that Excel.Workbook Function Delivers.
10. (04:02) Two-Way Lookup with Positional Access Operator and Field Access Operator to extract the Sheet Object.
11. (05:09) Build First Custom Power Query Function using User Interface.
12. (05:24) Extract Sample Table to build M Code for Custom Function.
13. (06:03) Build M Code for Custom Function using User Interface with two Remove Blank Roes and Two Transpose Operations.
14. (06:57) Copy Code from Advanced Editor.
15. (07:08) Build First Custom Function in Blank Query.
16. (07:28) Understanding Let Statements in Power Query M Code.
17. (08:12) Define Custom Function with Variable and Go To Operator.
18. (09:01) Invoke Custom Function For Custom Column. Use Custom Function on Excel Sheet Data to Remove Blank Rows and Columns.
19. (09:45) Append Process For First Example.
20. (10:320 Load first example to Worksheet.
21. (10:48) Build Second Custom Power Query Function typing more efficient M Code.
22. (11:13) Second argument in Excel.Workbook Function to promote Headers (Field Names). For example #2, we Promote Headers.
23. (11:45) Conceptual Approach for Second M Code Custom Function. Code from Bill Szysz.
24. (12:36) Create Blank Query for Second Custom Function.
25. (12:53) How do we Visualize M Code, given that there is no F9 key, like in an Excel Worksheet Formula? See the Power Query M Code Function; Table.ColumnNames.
26. (14:40) Connect Function Variables to Columns.
27. (15:00) Power Query M Code Functions List.Select and Text.StartsWith.
28. (16:40) Table.SelectColumns Power Query M Code Function.
29. (17:08) Table.SelectRows and Record.Field Power Query M Code Function. See not comparative operator and the literal null.
30. (18:36) Table.ColumnNames with Positional Index Operator for first row.
31. (19:36) Use Variable for First Column Name.
32. (19:53) Table.AddColumn Power Query M Code Function.
33. (20:36) Table.Combine Power Query M Code Function.
34. (21:21) Load Second Appended Tables.
35. (21:31) Add new data and refresh.
36. (21:41) Summary and End Video Links