Download Power BI Desktop START File:
Excel Start: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerQuery/010-MSPTDA-CustomFunction-MovingAnnualTotals-Start.pbix
Zipped Folder with Text Start Files: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerQuery/010-TextFiles.zip
Download Power BI Desktop FINISHED File: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerQuery/010-MSPTDA-CustomFunction-MovingAnnualTotals-Finished.pbix
Download pdf Notes about Power Query: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerQuery/010-MSPTDA-CustomFunction-MovingAnnualTotals.pdf
Download Excel File with parallel Excel Example: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerQuery/010-MSPTDA-CustomFunction-MovingAnnualTotals-Excel.xlsx
Assigned Homework:
Download pdf file with homework description: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerQuery/010-MSPTDA-Homework-Start.pdf
Example of Finished Homework in Power BI Desktop: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/PowerQuery/010-MSPTDA-Homework-Finished.pbix


In this Video learn Power Query M Code and Custom Functions to calculate Moving Annual Toatls.
Topics:
1. (00:15) Introduction
2. (01:10) Comment from YouTube that inspired the video. Verbal Description of the Data Model Transformation we want to make, including the Moving Annual Total Calculation.
3. (02:07) Thanks to Bill Szysz for Custom Function.
4. (02:18) Excel Example of Moving Annual Total
5. (03:30) Why Power Query and not Excel or DAX?
6. (03:43) Look at final solution and Custom Function to see what we are trying to accomplish, including a method to filter a table with in a Custom Column in Another Table and have the formula see criteria from the the Inner Table and the Outer Table.
7. (05:37) Step 1: Look at how we imported files
8. (06:07) Step 2: Extract a Sorted Unique List from the source Facet Table. Use Production Operator to get a List, then use the Table.Distinct and Table.Sort functions.
9. (07:31) Step 3: M Code to create a Crossjoin of all combinations of Months and Product Names with the steps: Extract Column, Convert to Start of Month, Extract Min and Max Dates, use List.Dates function to create range of dates, then merge using Custom Column to get all combinations of Months and dates.
10. (14:39) Step 4: Group BY Date and Product to get Monthly Totals.
11. (16:25) Step 5: Create Final Table with the steps: Merge Step 3 and Step 4, Remove Nulls, Add Custom Column to get One Year Back.
12. (20:15) Step 5: Sort in Power Query, and how it is different than Sort in Excel.
13. (21:25) Step 5: Table.Buffer Function allows us to Buffer the Internal Table to prevent a call to the source table for every row in the table.
14. (22:22) Step 5: create Custom Column with Function to Calculate Moving Annual Totals (MAT).
15. (28:41) Add new data to test if everything updates
16. (29:06) Summary

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.