Power BI M Code for Moving Annual Total - Custom Function Power Query Custom Column

Overview

Learn how to create a custom function in Power BI using M code to calculate Moving Annual Totals (MAT) in this comprehensive tutorial. Explore the process of transforming data models, importing files, and manipulating tables using Power Query. Master techniques such as extracting sorted unique lists, creating crossjoins of dates and products, grouping data for monthly totals, and implementing custom columns. Discover the differences between sorting in Power Query and Excel, and understand the importance of the Table.Buffer function. By the end of this video, you'll be able to create dynamic MAT calculations that update automatically with new data, enhancing your data analysis capabilities in Power BI.

Syllabus

) Introduction .
) Comment from YouTube that inspired the video. Verbal Description of the Data Model Transformation we want to make, including the Moving Annual Total Calculation..
) Thanks to Bill Szysz for Custom Function..
) Excel Example of Moving Annual Total.
) Why Power Query and not Excel or DAX?.
) 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..
) Step 1: Look at how we imported files.
) 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..
) 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..
) Step 4: Group BY Date and Product to get Monthly Totals..
) Step 5: Create Final Table with the steps: Merge Step 3 and Step 4, Remove Nulls, Add Custom Column to get One Year Back..
) Step 5: Sort in Power Query, and how it is different than Sort in Excel..
) 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..
) Step 5: create Custom Column with Function to Calculate Moving Annual Totals (MAT)..
) Add new data to test if everything updates.
) Summary.