Excel Magic Trick 1357: Power Query Function to Repeat Clean & Transform Steps for Many Excel Files

Download Files: Start File: https://excelisfun.net/files/EMT1357Start.xlsx https://excelisfun.net/files/1357SourceFiles.zip Finish File: https://excelisfun.net/files/EMT1357Finished.xlsx Download File: http://people.highline.edu/mgirvin/excelisfun.htm Goal: Unpivot and Clean Cross Tabulated Tables in multiple Excel Workbook Files and Create a Single Proper Data Set. Create a Power Query Function to repeatedly perform the Clean & Transform Task across many Excel Files. Lean how to use the new Invoke Custom Function option for a Custom Column. Learn many powerful features in Power Query (Get & Transform): 1. (00:16) Introduction: Overview of whole process, including looking at the files that we need to import, clean, transform and consolidate 2. (02:00) Build Custom Power Query Function with the steps listed below (3 – 12) 3. (03:47) Transpose Table (to deal with the fact that there are two column headers with conditions that need to be Unpivoted) 4. (04:30) Promote Headers 5. (04:48) UnPivot Other Columns based on the two columns Date and Fair 6. (05:40) Rename Columns 7. (06:05) Close and Load to “Create Connection Only” 8. (06:34) Duplicate Query 9. (06:52) Look at M Code and how it is set up and automatically written when you use the User Interface in Power Query 10. (08:27) Add lines of M Code to convert duplicated query to a Custom Power Query Function 11. (09:42) Import Files From Folder: Import Multiple Excel Files with Cross Tabulated Tables that need to be cleaned and Unpivoted. 12. (10:34) Transform extension column to lowercase letters and then Filter for only Excel Files with extension “.xlsx” 13. (11:25) Add Custom Column with Excel.Workbook Function to get Excel Objects, such as Sheets. 14. (12:58) Expand Custom Column to show objects, and to expose sheets with the Cross Tabulated Tables. 15. (13:37) Filter to import only Sheet Objects. 16. (14:09) Filter Out Sheet Tabs that contain the word “Sheet” (Sheet Tabs that do not have a Sales Rep Name. 17. (14:30) Remove Other Columns (Not Data or Name) 18. (14:42) Invoke Custom Power Query Function 19. (14:34) Remove Data Column 20. (14:39) Expand Columns 21. (15:55) Set Data Types and rename columns 22. (16:34) Load to Excel Worksheet (Table, Only Create Connection or Data Model) 23. (17:36) Test Updating by dropping new files in our folder 24. (18:20) Summary