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):
(00:16) Introduction: Overview of whole process, including looking at the files that we need to import, clean, transform and consolidate
(02:00) Build Custom Power Query Function with the steps listed below (3 – 12)
(03:47) Transpose Table (to deal with the fact that there are two column headers with conditions that need to be Unpivoted)
(04:30) Promote Headers
(04:48) UnPivot Other Columns based on the two columns Date and Fair
(05:40) Rename Columns
(06:05) Close and Load to “Create Connection Only”
(06:34) Duplicate Query
(06:52) Look at M Code and how it is set up and automatically written when you use the User Interface in Power Query
(08:27) Add lines of M Code to convert duplicated query to a Custom Power Query Function
(09:42) Import Files From Folder: Import Multiple Excel Files with Cross Tabulated Tables that need to be cleaned and Unpivoted.
(10:34) Transform extension column to lowercase letters and then Filter for only Excel Files with extension “.xlsx”
(11:25) Add Custom Column with Excel.Workbook Function to get Excel Objects, such as Sheets.
(12:58) Expand Custom Column to show objects, and to expose sheets with the Cross Tabulated Tables.
(13:37) Filter to import only Sheet Objects.
(14:09) Filter Out Sheet Tabs that contain the word “Sheet” (Sheet Tabs that do not have a Sales Rep Name.
(14:30) Remove Other Columns (Not Data or Name)
(14:42) Invoke Custom Power Query Function
(14:34) Remove Data Column
(14:39) Expand Columns
(15:55) Set Data Types and rename columns
(16:34) Load to Excel Worksheet (Table, Only Create Connection or Data Model)
(17:36) Test Updating by dropping new files in our folder