Join SuperDataScience
Start Your Free Trial Today >>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.
The course takes your through these essential four steps:
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.
It is structured the following way:
Module 1 - Getting Started
Module 2 - Intro to "Power Excel"
Module 3 - Power Query
Module 4 - Data Modeling 101
Module 5 - Power Pivot & DAX 101
Module 6 - Common DAX Functions
Module 7 - Wrapping Up
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!
Chris
$35
/ MonthAccelerate your Career and boost your Earning Potential with our Expert Instructors & Community! What Youāll Unlock: - š 40+ Courses (over 200 hours!) - š 17 Specialized Career Paths - š Quizzes and Practice Activities - š Certificates for Courses & Career Paths - š Prizes for Learning - š§Ŗ Weekly Live Labs (plus recordings!) - šÆ Monthly Missions to practice even more - š¼ Monthly Career Booster Events - š¬ Full access to the SDS Community - ā” Monthly Speed Networking - š„ Monthly Resume Clinics - š„ Group Mentorship Program "In just a few months of learning at SDS, I landed a Data Analyst job!" ā Sanaz Afshar, California
$157
/ MonthAccelerate your Career and boost your Earning Potential with our Expert Instructors & Community! What Youāll Unlock: - š 40+ Courses (over 200 hours!) - š 17 Specialized Career Paths - š Quizzes and Practice Activities - š Certificates for Courses & Career Paths - š Prizes for Learning - š§Ŗ Weekly Live Labs (plus recordings!) - šÆ Monthly Missions to practice even more - š¼ Monthly Career Booster Events - š¬ Full access to the SDS Community - ā” Monthly Speed Networking - š„ Monthly Resume Clinics - š„ Group Mentorship Program Plus, Pro Plan perks just for you: - š A Personalized Career Path built around your goals - š§āš« 1-on-1 Mentoring Sessions every month - āļø Personalized Resume Reviews "In just a few months of learning at SDS, I landed a Data Analyst job!" ā Sanaz Afshar, California
Course content
Introduction
00:27
Meet Power Query (aka āGet & Transformā)
02:41
The Query Editor
03:17
Data Loading Options
01:40
Important: Regional Date Formatting
Basic Table Transformations
08:06
Text-Specific Tools
10:18
Number-Specific Tools
07:42
Date-Specific Tools
07:40
Pro Tip: Creating a Rolling Calendar
05:02
Adding Index & Conditional Columns
07:44
Grouping & Aggregating data
07:10
Pivoting & Unpivoting
08:53
Modifying Workbook Queries
04:18
Merging Queries
07:43
Appending Queries
05:49
Connecting to a Folder of Files
09:33
Power Query Best Practices
02:44
Quiz: Power Query
Homework: Power Query
Introduction
00:29
Meet Excel's "Data Model"
03:38
Data vs. Diagram View
02:33
Database Normalization
04:15
Data Tables vs. Lookup Tables
05:40
Relationships vs. Merged Tables
02:08
Creating Table Relationships
06:55
Modifying Table Relationships
02:07
Active vs. Inactive Relationships
03:55
Relationship Cardinality
05:47
Connecting Multiple Data Tables
05:59
Filter Direction
05:08
Hiding Fields from Client Tools
05:00
Defining Hierarchies
03:54
Data Model Best Practices
01:38
Quiz: Data Modeling 101
Homework: Data Modeling 101
Introduction
00:29
Creating a "Power" PivotTable
05:27
Power Pivots vs. āNormalā Pivots
02:49
Intro to Data Analysis Expressions (DAX)
01:12
Calculated Columns
08:40
DAX Measures = Supercharged Calculated Fields
02:24
Creating Implicit Measures
02:00
Creating Explicit Measures (AutoSum)
07:09
Creating Explicit Measures (Power Pivot)
07:42
Understanding Filter Context
09:19
Step-by-Step Measure Calculation
12:07
Recap: Calculated Columns vs. Measures
02:29
Power Pivot Best Practices
01:51
Quiz: Power Pivot & DAX 101
Homework: Power Pivot & DAX 101
Introduction
00:28
DAX Formula Syntax & Operators
04:05
Common DAX Function Categories
03:55
Basic Math & Stats Functions
12:46
COUNT, COUNTA, DISTINCTCOUNT & COUNTROWS
10:36
Homework: Math & Stats Functions
Logical Functions (IF/AND/OR)
16:49
SWITCH & SWITCH(TRUE)
10:33
Text Functions
10:54
Homework: Logical & Text Functions
The CALCULATE Function
14:39
Adding Filter Context with FILTER (Part 1)
09:20
Adding Filter Context with FILTER (Part 2)
14:24
Removing Filter Context with ALL
09:06
Homework: CALCULATE, FILTER & ALL
Joining Data with RELATED
03:39
Iterator ("X") Functions: SUMX
12:21
Iterator ("X") Functions: RANKX
05:33
Homework: Iterator Functions
Basic Date & Time Functions
04:59
Time Intelligence Formulas
17:02
Homework: Time Intelligence
Speed & Performance Considerations
03:03
DAX Best Practices
02:04
Quiz: Common DAX Functions