MSPTDA 33: Data Modeling for Slowly Changing Dimensions with Power Query & PivotTable
11.4 هزار بار بازدید -
5 سال پیش
-
Download Start Excel file:
Download Start Excel file: https://people.highline.edu/mgirvin/A...
Download Finished Excel file: https://people.highline.edu/mgirvin/A...
Pdf files with notes: https://people.highline.edu/mgirvin/A...
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.
Download Finished Excel file: https://people.highline.edu/mgirvin/A...
Pdf files with notes: https://people.highline.edu/mgirvin/A...
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.
5 سال پیش
در تاریخ 1398/07/25 منتشر شده
است.
11,448
بـار بازدید شده