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

Download Files:

Start File: https://excelisfun.net/files/EMT1357S...

https://excelisfun.net/files/1357Sour...

Finish File: https://excelisfun.net/files/EMT1357F...

Download File: http://people.highline.edu/mgirvin/ex...
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