Intro to Power Excel

• Understanding the “Power Excel” Workflow
• When to use Power Query & Power Pivot

Connecting and 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

Building Table Relationships with Excels 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

Analyzing Data With Power Pivot and DAX

• Introduction
• Creating a “Power” Pivot Table
• Power Pivots s. “ 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

Common DAX Formulas and Functions

• introduction
• Understanding DAX Formula Syntax & Operators
• Common DAX Function Categories
• DAX Demo: Basic Math & Stats Functions
• DAX Demo: COUNT, COUNTA, DISTINCTCOUNT & COUNTROWS
• DAX Demo: Logical Functions (IF/AND/OR)
• DAX Demo: SWITCH & SWITCH(TRUE)
• DAX Demo: Common 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
• DAX Demo: Joining Data with RELATED
• DAX Demo: Iterating with SUMX
• DAX Demo: Iterating with RANKX