Module 1 Getting Started
Unit 1 Course Structure & Outline
Unit 2 IMPORTANT: Versions & Compatibility
Unit 3 Downloading the Course Project Files
Unit 4 Setting Expectations
Module 2 Intro to "Power Excel"
Unit 1 The “Power Excel” Workflow
Unit 2 "The Best Thing to Happen to Excel in 20 Years"
Unit 3 When to use Power Query & Power Pivot
Unit 4 QUIZ: Intro to "Power Excel"
Unit 5 HOMEWORK: Intro to "Power Excel"
Module 3 Power Query
Unit 1 Introduction
Unit 2 Meet Power Query (aka “Get & Transform”)
Unit 3 The Query Editor
Unit 4 Data Loading Options
Unit 5 IMPORTANT: Regional Date Formatting
Unit 6 Basic Table Transformations
Unit 7 Text-Specific Tools
Unit 8 Number-Specific Tools
Unit 9 Date-Specific Tools
Unit 10 PRO TIP: Creating a Rolling Calendar
Unit 11 Adding Index & Conditional Columns
Unit 12 Grouping & Aggregating data
Unit 13 Pivoting & Unpivoting
Unit 14 Modifying Workbook Queries
Unit 15 Merging Queries
Unit 16 Appending Queries
Unit 17 Connecting to a Folder of Files
Unit 18 Power Query Best Practices
Unit 19 QUIZ: Power Query
Unit 20 HOMEWORK: Power Query
Module 4 Data Modeling 101
Unit 1 Introduction
Unit 2 Meet Excel's "Data Model"
Unit 3 Data vs. Diagram View
Unit 4 Database Normalization
Unit 5 Data Tables vs. Lookup Tables
Unit 6 Relationships vs. Merged Tables
Unit 7 Creating Table Relationships
Unit 8 Modifying Table Relationships
Unit 9 Active vs. Inactive Relationships
Unit 10 Relationship Cardinality
Unit 11 Connecting Multiple Data Tables
Unit 12 Filter Direction
Unit 13 Hiding Fields from Client Tools
Unit 14 Defining Hierarchies
Unit 15 Data Model Best Practices
Unit 16 QUIZ: Data Modeling 101
Unit 17 HOMEWORK: Data Modeling 101
Module 5 Power Pivot & DAX 101
Unit 1 Introduction
Unit 2 Creating a "Power" PivotTable
Unit 3 Power Pivots vs. “Normal” Pivots
Unit 4 Intro to Data Analysis Expressions (DAX)
Unit 5 Calculated Columns
Unit 6 DAX Measures = Supercharged Calculated Fields
Unit 7 Creating Implicit Measures
Unit 8 Creating Explicit Measures (AutoSum)
Unit 9 Creating Explicit Measures (Power Pivot)
Unit 10 Understanding Filter Context
Unit 11 Step-by-Step Measure Calculation
Unit 12 Recap: Calculated Columns vs. Measures
Unit 13 Power Pivot Best Practices
Unit 14 QUIZ: Power Pivot & DAX 101
Unit 15 HOMEWORK: Power Pivot & DAX 101
Module 6 Common DAX Functions
Unit 1 Introduction
Unit 2 DAX Formula Syntax & Operators
Unit 3 Common DAX Function Categories
Unit 4 Basic Math & Stats Functions
Unit 6 HOMEWORK: Math & Stats Functions
Unit 7 Logical Functions (IF/AND/OR)
Unit 9 Text Functions
Unit 10 HOMEWORK: Logical & Text Functions
Unit 11 The CALCULATE Function
Unit 12 Adding Filter Context with FILTER (Part 1)
Unit 13 Adding Filter Context with FILTER (Part 2)
Unit 14 Removing Filter Context with ALL
Unit 16 Joining Data with RELATED
Unit 17 Iterator ("X") Functions: SUMX
Unit 18 Iterator ("X") Functions: RANKX
Unit 19 HOMEWORK: Iterator Functions
Unit 20 Basic Data & Time Functions
Unit 21 Time Intelligence Formulas
Unit 22 HOMEWORK: Time Intelligence
Unit 23 Speed & Performance Considerations
Unit 24 DAX Best Practices
Unit 25 QUIZ: Common DAX Functions
Module 7 Wrapping Up
Unit 1 Introduction
Unit 2 Data Visualization Options
Unit 3 Sneak Peek: PowerBI
Unit 4 Resources & Next Steps
Unit 5 BONUS LECTURE: More from Excel Maven
Powered By WP Courseware

Microsoft Excel – Intro to Power Query, Power Pivot & DAX


Course Description

This course introduces Microsoft Excel's powerful data modeling and business intelligence tools: Power Query, Power Pivot, and Data Analysis Expressions (DAX).
If you're looking to become a power Excel user and absolutely supercharge your analytics, this course is the A-Z guide that you're looking for.

We'll kick things off by introducing the “Power Excel” landscape, and explore what these tools are all about and why they are changing the world of self-service business intelligence.

Using sample data from a fictional supermarket chain, we'll get hands-on with Power Query; a tool to extract, transform, and load data from flat files, folders, databases, API services and more. We'll practice shaping, blending and exploring our project files, and create completely automated loading procedures with only a few clicks.

From there we'll dive into Data Modeling 101, and cover the fundamentals of database design and normalization (including table relationships, cardinality, hierarchies and more). We'll take a tour through Excel's data model interface, introduce some best practices and pro tips, and then create our own relational database to analyze throughout the course.

Next, we'll use Power Pivot and DAX to explore and analyze our data model. Unlike traditional pivots, Power Pivot allows you to analyze hundreds of millions of rows across multiple data tables, and create supercharged calculated fields using a formula language called Data Analysis Expressions (or “DAX” for short). We'll cover basic DAX syntax, then introduce some of the most powerful and commonly-used functions — CALCULATE, FILTER, SUMX and more.

If you're ready to take your Excel game to new heights and join the leading edge of analytics & business intelligence, this course is for you. It's time to stop fighting with tedious, manual tasks and struggling with “old-school” Excel; join me on this journey and emerge a certified NINJA.

See you in there!