Download Start Excel file: https://ift.tt/2qg0tF1
Download Finished Excel file: https://ift.tt/2W0DmKJ
Pdf files with notes: https://ift.tt/2IOoqtJ
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.