Completed
) Comment from YouTube that inspired the video. Verbal Description of the Data Model Transformation we want to make, including the Moving Annual Total Calculation.
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
