Getting Started
  • Course Structure & Outline
  • IMPORTANT: Versions & Compatibility
  • READ ME: Important Notes for New Students
  • Introducing the Course Project
  • DOWNLOAD: Course Resources
  • Setting Expectations
Intro to “Power Excel"
  • Understanding the “Power Excel” Workflow
  • Power Query + Power Pivot: "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"
Connecting & Transforming Data with Power Query in Excel
  • Introduction
  • Getting to Know Power Query in Excel
  • Exploring Excel's Power Query Editor
  • Power Query Data Loading Options
  • IMPORTANT: Updating Locale Settings
  • Applying Basic Table Transformations with Power Query
  • Power Query Demo: Text Tools
  • Power Query Demo: Number & Value Tools
  • Power Query Demo: Date & Time Tools
  • PRO TIP: Creating a Rolling Calendar with Power Query
  • Power Query Demo: Generating Index & Conditional Columns
  • Power Query Demo: Grouping & Aggregating Records
  • Power Query Demo: Pivoting & Unpivoting Data
  • Modifying Excel Workbook Queries
  • Merging Queries with Power Query
  • Appending Queries with Power Query
  • Power Query Demo: Connecting to a Folder of Files
  • Excel Power Query Best Practices
  • QUIZ: Connecting & Transforming Data with Power Query in Excel
  • HOMEWORK: Connecting & Transforming Data with Power Query in Excel
Building Table Relationships with Excel's Data Model
  • Introduction
  • Meet Excel's "Data Model"
  • The Data Model Data vs. Diagram View
  • Principles of Database Normalization
  • Understanding Data Tables vs. Lookup Tables
  • Benefits of Relationships vs. Merged Tables
  • Creating Table Relationships in Excel's Data Model
  • Modifying Data Model Table Relationships
  • Managing Active vs. Inactive Table Relationships
  • Understanding Relationship Cardinality
  • Connecting Multiple Data Tables in the Data Model
  • Understanding Filter Flow
  • Hiding Fields from Excel Client Tools
  • Defining Hierarchies in a Data Model
  • Excel Data Model Best Practices
  • QUIZ: Building Table Relationships with Excel's Data Model
  • HOMEWORK: Building Table Relationships with Excel's Data Model
Analyzing Data with Power Pivot & DAX
  • Introduction
  • Creating a "Power" Pivot Table
  • Power Pivots vs. “Normal” Pivots in Excel
  • Introducing Data Analysis Expressions (DAX)
  • Understanding DAX Calculated Columns
  • Understanding DAX Measures
  • Creating Implicit DAX Measures
  • Creating Explicit DAX Measures with AutoSum
  • Creating Explicit DAX Measures with Power Pivot
  • Understanding DAX Filter Context
  • Step-by-Step DAX Measure Calculation
  • RECAP: Calculated Columns vs. DAX Measures
  • Excel Power Pivot & DAX Best Practices
  • QUIZ: Analyzing Data with Power Pivot & DAX
  • HOMEWORK: Analyzing Data with Power Pivot & DAX
Common DAX Formulas & Functions
  • Introduction
  • Understanding DAX Formula Syntax & Operators
  • Common DAX Function Categories
  • DAX Demo: Basic Math & Stats Functions
  • DAX Demo: COUNT, COUNTA, DISTINCTCOUNT & COUNTROWS
  • HOMEWORK: Math & Stats Functions
  • DAX Demo: Logical Functions (IF/AND/OR)
  • DAX Demo: SWITCH & SWITCH(TRUE)
  • DAX Demo: Common Text Functions
  • HOMEWORK: Logical & Text Functions
  • DAX Demo: CALCULATE
  • DAX Demo: Adding Filter Context with FILTER (Part 1)
  • DAX Demo: Adding Filter Context with FILTER (Part 2)
  • DAX Demo: Removing Filter Context with ALL
  • HOMEWORK: CALCULATE, FILTER & ALL
  • DAX Demo: Joining Data with RELATED
  • DAX Demo: Iterating with SUMX
  • DAX Demo: Iterating with RANKX
  • HOMEWORK: Iterator ("X") Functions
  • DAX Demo: Basic Date & Time Functions
  • DAX Demo: Time Intelligence Formulas
  • HOMEWORK: Time Intelligence
  • DAX Speed & Performance Considerations
  • DAX Best Practices
  • QUIZ: Common DAX Formulas & Functions
Wrapping Up
  • Introduction
  • Data Visualization Options in Excel
  • Sneak Peek: Microsoft Power BI
  • BONUS LECTURE: More from Maven Analytics