Download Start Excel file: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DataModeling/MSPTDA-033-ChangingDimensions-PQAndStandardPivotTableStart.xlsx
Download Finished Excel file: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DataModeling/MSPTDA-033-ChangingDimensions-PQAndStandardPivotTableFinished.xlsx
Pdf files with notes: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DataModeling/032-35-MSPTDA-ChangingDimensions.pdf

In this video learn about how to deal with a “Slowly Changing Dimensions”, which is the Dimension = Employee and the changing Attribute = Team. Learn how to use Power Query to transform the Dimension Table and Fact Table so that we can show Total Sales by Team and Employee using a Standard PivotTable. Learn how to create automatic steps in Power Query and Custom M Code.
Topics:
1. (00:08) Introduction to Data Setup and Goal of Power Query Data Modeling
2. (01:44) Power Query Transformation for Dimension Table, including UnPivot Other Columns.
3. (03:30) Power Query M Code Formula to pull Team Key into Fact Table based on three conditions / criteria. See the functions Table.AddColumn and Table.SelectRows, and how to Define a Variable for a Custom Function. The Formula does a Three Way Lookup in Power Query.
4. (11:09) Build Standard PivotTable Report, including Loading Power Query Output directly to a Standard PivotTable.
5. (11:57) Summary

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