Download Excel Start File: https://excelisfun.net/files/EMT1621-Start.xlsx
Download Excel Finished File: https://excelisfun.net/files/EMT1621-Finished.xlsx

Learn how to take 12 Excel Worksheets with cross tabulated tables and convert them to a proper data set and then a PivotTable Report to show the amount of change and 5 Change for each Cow at the Farm from the Morning Shift to the Evening Shift. Many Power Query M Code tips in the video, including how to create an M Code Custom Function to convert each Cross Tabulated Sheet Data to One Single Proper Data Set. Software Train at YouTube suggest a new way to invoke Excel.Workbook.
1. (00:01) Introduction
2. (01:20) Bring File Path Address into Power Query and name it “DataInProperDataSet”.
3. (02:50) File.Contents M Code Function, as a new step, to Invoke Excel.Workbook M Code Function so we can get the objects in the Excel File. This trick comes from . Software Train at YouTube.
4. (03:39) Look at arguments of Excel.Workbook Power Query Function (M Code Function)
5. (04:09) Filter out unwanted sheets.
6. (04:40) Close and Load DataInProperDataSet Query as Connection Only.
7. (04:53) Duplicate Query, Extract a single Sheet and make transformation to convert Cross Tab Sheet Data to a Proper Data Set. This step is where we create the code that can Unpivot a single cross tab table into a Proper Data Set.
8. (05:57) Promote Headers.
9. (06:09) Filter Total Rows Out.
10. (06:30) Create Conditional Column to create new column with Cow Names.
11. (07:10) Fill Cow Names Down.
12. (07:15) See the Table.UnpivotOtherColumns Function to consolidate all dates (columns headers) and Cow Milk Amounts into Two New Columns.
13. (08:11) Filter out blank rows.
14. (08:21) Change Data Types.
15. (08:30) Load “BuildFunction” query as Connection Only.
16. (08:37) Duplicate “BuildFunction”, and add Custom Function Syntax to Convert the Query to a Re-usable Function. We use the Advanced Editor to edit the M Code.
17. (10:06) Define Power Query M Code Custom Function: 1) Variables in Parenthesis, 2) Go To Operator, 3) Mapping of the Variable.
18. (10:28) Invoke Custom Function in the “DataInProperDataSet” function to convert 12 Cross Tab Tables into 12 Proper Data Sets.
19. (11:00) Append all 12 Proper Data sets into a single Proper Data Set using Table Lookup Operators and the Table.Combine Function.
20. (11:43) Change Load Connection to Load Single Proper Data Set to the PivotTable Cache.
21. (12:04) Learn how to Manually Edit the Custom Function when we determine that there has been an error in the M Code for our Custom Function.
22. (12:52) Learn about fundamentals of a let statement and M Code.
23. (13:46) Open Advanced Editor for an M Code Power Query Custom Function.
24. (14:15) Build PivotTable Report that uses Show Values As Difference From and Show Values As % Difference From.
25. (15:02) Summary