IIRF Online > Business > Business Analytics & Intelligence > Data Modeling > Microsoft Excel: Business Intelligence w/ Power Query & DAX
Microsoft Excel: Business Intelligence w/ Power Query & DAX by Udemy
Master Excel Power Query, Power Pivot & DAX for advanced data analysis & business intelligence w/ a top Excel instructor
Course Highlights
- Get up and running with Excel's powerful data modeling & business intelligence tools
- Learn how to use Power Query, Power Pivot & DAX to absolutely revolutionize your workflow in Excel
- Master unique tips, tools and case studies that you won't find in ANY other course, guaranteed
- Explore fun, interactive, and highly effective lessons from a best-selling Excel instructor
- Get LIFETIME access to project files, quizzes and homework exercises, and 1-on-1 expert support
- Build pro-quality business intelligence solutions to blend and analyze data from multiple sources
Skills you will learn!
Curriculum
6 Topics
Course Structure & Outline
IMPORTANT: Versions & Compatibility
READ ME: Important Notes for New Students
Introducing the Course Project
DOWNLOAD: Course Resources
Setting Expectations
5 Topics
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"
21 Topics
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
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
17 Topics
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
15 Topics
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
25 Topics
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
4 Topics
Introduction
Data Visualization Options in Excel
Sneak Peek: Microsoft Power BI
BONUS LESSON
Microsoft Excel: Business Intelligence w/ Power Query & DAX
Related Data Modeling Courses
Thank You!
Your review has been submitted successfully.