Completed
) Thanks to Bill Szysz for Custom Function.
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
Power BI M Code for Moving Annual Total - Custom Function Power Query Custom Column
Automatically move to the next video in the Classroom when playback concludes
- 1 ) Introduction
- 2 ) 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 ) Thanks to Bill Szysz for Custom Function.
- 4 ) Excel Example of Moving Annual Total
- 5 ) Why Power Query and not Excel or DAX?
- 6 ) 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 f…
- 7 ) Step 1: Look at how we imported files
- 8 ) 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 ) 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…
- 10 ) Step 4: Group BY Date and Product to get Monthly Totals.
- 11 ) 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 ) Step 5: Sort in Power Query, and how it is different than Sort in Excel.
- 13 ) 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 ) Step 5: create Custom Column with Function to Calculate Moving Annual Totals (MAT).
- 15 ) Add new data to test if everything updates
- 16 ) Summary
