Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Free Preview: Power Query, Power Pivot & DAX
Getting Started
About this Preview Course
Full Course Structure & Outline (1:47)
IMPORTANT: Versions & Compatibility (3:15)
Exploring the Course Project Files (1:16)
Setting Expectations (1:53)
Intro to "Power Excel"
The "Power Excel" Workflow (3:50)
"Best Thing to Happen to Excel in 20 Years" (8:18)
When to use Power Query & Power Pivot
QUIZ: Intro to "Power Excel"
HOMEWORK: Intro to "Power Excel"
Connecting & Shaping Data with Power Query
Introduction (0:27)
Meet Power Query (aka "Get & Transform")
The Query Editor (3:17)
Data Loading Options
Basic Table Transformations (8:06)
Text-Specific Tools
Number-Specific Tools
Date-Specific Tools (7:39)
PRO TIP: Creating a Rolling Calendar
Adding Index & Conditional Columns
Grouping & Aggregating Data
Pivoting & Unpivoting (8:52)
Modifying Workbook Queries
Merging Queries
Appending Queries
Connecting to a Folder of Files
Power Query Best Practices (2:44)
QUIZ: Power Query
HOMEWORK: Power Query
Data Modeling & Normalization
Introduction (0:28)
Meet Excel's "Data Model" (3:38)
Data vs. Diagram View
Database Normalization
Data Tables vs. Lookup Tables (5:40)
Relationships vs. Merged Tables
Creating Table Relationships (6:54)
Modifying Table Relationships
Active vs. Inactive Relationships
Relationship Cardinality (5:46)
Connecting Multiple Data Tables
Filter Direction
Hiding Fields from Client Tools
Defining Hierarchies (3:53)
Data Model Best Practices
QUIZ: Data Modeling 101
HOMEWORK: Data Modeling 101
Power Pivot & DAX
Introduction (0:29)
Creating a "Power" PivotTable
"Power" Pivots vs. "Normal" Pivots (2:48)
Intro to Data Analysis Expressions (DAX) (1:12)
Calculated Columns
DAX Measures = Supercharged Calculated Fields
Creating Implicit Measures
Creating Explicit Measures (AutoSum)
Creating Explicit Measures (Power Pivot) (7:42)
Understanding Filter Context
Step-by-Step Measure Calculation
RECAP: Calculated Columns vs. Measures (2:29)
Power Pivot Best Practices
QUIZ: Power Pivot & DAX 101
HOMEWORK: Power Pivot & DAX 101
DAX Formulas & Functions
Introduction (0:27)
DAX Formula Syntax & Operators (4:05)
Common DAX Function Categories
Basic Math & Stats Functions
COUNT, COUNTA, DISTINCTCOUNT & COUNTROWS (10:35)
HOMEWORK: Math & Stats Functions
Logical Functions (IF/AND/OR)
SWITCH & SWITCH(TRUE)
Text Functions
HOMEWORK: Logical & Text Functions
The CALCULATE Function (14:39)
Adding Filter Context with FILTER (Part 1)
Adding Filter Context with FILTER (Part 2)
Removing Filter Context with ALL (9:06)
HOMEWORK: CALCULATE, FILTER & ALL
Joining Data with RELATED
Iterator ("X") Functions: SUMX (12:20)
Iterator ("X") Functions: RANKX
HOMEWORK: Iterator Functions
Basic Date & Time Functions
Time Intelligence Formulas
HOMEWORK: Time Intelligence
Speed & Performance Considerations (3:02)
DAX Best Practices
QUIZ: Common DAX Functions
Wrapping Up
Introduction (0:53)
Data Visualization Options
SNEAK PEEK: Microsoft Power BI (5:55)
Resources & Next Steps
Special Offer
"Best Thing to Happen to Excel in 20 Years"
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock