Ah, the “Franken-Table.” You’ve probably created one: a worksheet with umpteen VLOOKUP()‘s referencing various other worksheets and perhaps (gasp!) even other workbooks.
It’s ugly and inefficient: you may not even be able to build the report you were hoping to without the workbook crashing.
Fortunately Microsoft has developed a Frankentable-killer: Power Pivot. This is a relational modeling technology that you can use from right within Excel to define relationships between datasets to build efficient, dynamic reports.
For as awesome as this technology is, it’s important to understand that the relational data model was not invented with Power Pivot: it’s a classic field of computer science and provided scaffolding for the Age of Information. That’s why I take a decent amount of time in this workshop to explain the origins and theories of relational models.
Power Pivot is just one plank of the new Microsoft BI platform, so I also take some time to help learners contextualize how these tools work together.
Get your copy of the guide below. You are welcome to use this learning guide at your school or workplace to guide workshops or for however you can benefit from it. Access to the learning guide is not access to a workshop itself. You can use this learning guide to conduct a workshop at your organization. Consider this guide more like a recipe or blueprint.
Download your free copy of this guide below, then check out the course outline.
Lesson 1: Power Pivot and Modern Excel
Objective: Student can contextual Power Pivot in the Microsoft BI stack
Description:
- Modern Excel and the “Power Platform”
- Power Query, Pivot, View: Oh my!
- DAX & M, Query & Pivot
Assets needed: None
Time: 25 minutes
Lesson 2: How relational models work
Objective: Student can evaluate relational models for best practices in modeling
Description:
- A relational model of data
- Database normalization
- From lookups from joins
- Table relationships & cardinality
- Filter directions
- Hierarchies
Exercises: Inspect a data model
Assets needed: Employee database
Time: 90 minutes
Lesson 3: From PivotTables to “Power” PivotTables
Objective: Student can perform row-wise data cleaning
Description:
- Importing data & creating models
- String and date functions
- Conditional logic
- Implicit & explicit measures
- Creating PivotTable reports
Exercises: Build a data model & PivotTable report
Assets needed: Employee database
Time: 120 minutes
Lesson 4: Up and running with DAX
Objective: Student can write and modify measures with DAX
Description:
- Counting and mathematics functions
- Conditional logic functions
- Filter functions
- Iterator functions
- Sorting & aggregation
Exercises: Drills
Assets needed: Employee database
Time: 120 minutes
Lesson 5: Intermediate DAX
Objective: Student can perform intermediate data analysis operations with DAX
Description:
- Time intelligence
- Dependent measures
- DAX Studio
- Creating Power View reports
Exercises: Drills
Assets needed: E-commerce database
Time: 90 minutes
Leave a Reply