Advance MS Excel with Power Query and DAX
What you'll learn
This course includes
Course content
Intro to “ POWER EXECL ”:
Understanding the “Power Excel” Workflow
When to use Power Query & Power Pivot
7 questions
HOMEWORK: Intro to "Power Excel"
Connecting & Transforming Data with Power Query in Excel:
Introduction
Getting to Know Power Query in Excel
The Query Editor is your command center when it comes to loading and transforming raw data in Excel using Power Query. In this lecture we'll take a tour of the tools that we'll use to transform and shape our data.
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
Modifying Excel Workbook Queries
Merging Queries with Power Query
Appending Queries with Power Query
Power Query Demo: Connecting to a Folder of Files
PRO TIP: Additional Data Connectors (Excel, MySQL, PDF, Web)
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 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
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
10 questions
HOMEWORK: Building Table Relationships with Excel's Data Model
Analyzing Data with Power Pivot & DAX :
Introduction
Creating a "Power" Pivot Table
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
10 questions
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
HOMEWORK: Time Intelligence
DAX Speed & Performance Considerations
DAX Best Practices
QUIZ: Common DAX Formulas & Functions
Wrapping up :
Introduction
Data Visualization Options in Excel