Self-Paced Course
Power Query, Power Pivot & DAX
Build and analyze relational data models using Excel’s powerful trio of self-service BI tools: Power Query, Power Pivot & DAX.
Course Description
It's time to introduce Excel's powerful trio of self-service BI tools: Power Query, Power Pivot, and Data Analysis Expressions (DAX). If you're serious about business intelligence, these are essential skills to master.
This course takes a project-based approach, and is designed to guide you through the entire BI workflow from start to finish. Using data from a fictional supermarket chain, we'll load and transform raw CSV files with Power Query, build our own relational data model from scratch, and explore and analyze our model using Power Pivot and DAX expressions.
If you're ready to become an elite Excel user and take your analytics skills to the next level,** this is the course for you**.
Course Outline
- Welcome to the Course!
- Benchmark Assessment
- Course Structure & Outline
- IMPORTANT: Versions & Compatibility
- Introducing the Course Project
- DOWNLOAD: Course Resources
- Setting Expectations
- The Power Excel Workflow
- The Best Thing to Happen to Excel in 20 Years
- When to use Power Query & Power Pivot
- QUIZ: Intro to Power Excel
- HOMEWORK: Intro to Power Excel
- Introduction
- Meet Power Query (aka Get & Transform)
- The Query Editor
- Data Loading Options
- IMPORTANT: Updating Locale Settings
- Basic Table Transformations
- Text-Specific Tools
- Number-Specific Tools
- Date-Specific Tools
- PRO TIP: Creating a Rolling Calendar
- Adding Index & Conditional Columns
- Grouping & Aggregating Data
- Pivoting & Unpivoting
- Modifying Workbook Queries
- Merging Queries
- Appending Queries
- Connecting to a Folder of Files
- Power Query Best Practices
- QUIZ: Power Query
- HOMEWORK: Power Query
- Introduction
- Meet Excel's Data Model
- Data vs. Diagram View
- Database Normalization
- Data Tables vs. Lookup Tables
- Relationships vs. Merged Tables
- Creating Table Relationships
- Modifying Table Relationships
- Active vs. Inactive Relationships
- Relationship Cardinality
- Connecting Multiple Data Tables
- Filter Direction
- Hiding Fields from Client Tools
- Defining Hierarchies
- Data Model Best Practices
- QUIZ: Data Modeling 101
- HOMEWORK: Data Modeling 101
- Introduction
- Creating a Power PivotTable
- Power Pivots vs. Normal Pivots
- Intro to Data Analysis Expressions (DAX)
- Calculated Columns
- Intro to DAX Measures
- Creating Implicit Measures
- Creating Explicit Measured (AutoSum)
- Creating Explicit Measures (Power Pivot)
- Understanding Filter Context
- Step-by-Step Measure Calculation
- RECAP: Calculated Columns vs. Measures
- Power Pivot Best Practices
- QUIZ: Power Pivot & DAX 101
- HOMEWORK: Power Pivot & DAX 101
- Introduction
- DAX Formula Syntax & Operators
- Common DAX Function Categories
- Basic Math & Stats Functions
- COUNT, COUNTA, DISTINCTCOUNT & COUNTROWS
- HOMEWORK: Math & Stats Functions
- Logical Functions (IF/AND/OR)
- SWITCH & SWITCH(TRUE)
- Text Functions
- HOMEWORK: Logical & Text Functions
- The CALCULATE Function
- Adding Filter Context with Filter (Part 1)
- Adding Filter Context with Filter (Part 2)
- Removing Filter Context with ALL
- HOMEWORK: CALCULATE, FILTER & ALL
- Joining Data with RELATED
- Iterator (X) Functions: SUMX
- Iterator (X) Functions: RANKX
- HOMEWORK: Iterator Functions
- Basic Date & Time Functions
- Time Intelligence Formulas
- Speed & Performance Considerations
- DAX Best Practices
- QUIZ: Common DAX Functions
- Introduction
- Data Visualization Options
- Sneak Peek: Power BI
- Final Assessment
- Course Feedback Survey
- Share the love!
- Next Steps
WHO SHOULD TAKE THIS COURSE?
Excel users who are looking for an introduction to advanced analytics and data modeling tools, or preparing to transition into a business intelligence role
Analysts or BI professionals using Excel to analyze large datasets (>1mm rows), connect to external sources, or build relational data models
Anyone seeking to explore Excel's business intelligence capabilities
WHAT ARE THE COURSE REQUIREMENTS?
Microsoft Excel for PC/Windows, compatible with Power Query & Power Pivot (Power Pivot is currently unavailable in Excel for Mac)
Experience with Excel PivotTables and formulas is strongly recommended
Start learning for FREE, no credit card required!
Every subscription includes access to the following course materials
- Interactive Project files
- Downloadable e-books
- Graded quizzes and assessments
- 1-on-1 Expert support
- 100% satisfaction guarantee
- Verified credentials & accredited badges
Ready to become a
data rockstar?
Start learning for free, no credit card required!