Microsoft Excel - Excel Power Query, Power Pivot & DAX
4.7 (8,437 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
46,594 students enrolled

Microsoft Excel - Excel Power Query, Power Pivot & DAX

Learn Excel business intelligence (Power Query, Power Pivot & DAX) with a best-selling Excel instructor (Excel 2013+)
Bestseller
4.7 (8,437 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
46,613 students enrolled
Last updated 5/2020
English
English [Auto-generated], French [Auto-generated], 8 more
  • German [Auto-generated]
  • Indonesian [Auto-generated]
  • Italian [Auto-generated]
  • Polish [Auto-generated]
  • Portuguese [Auto-generated]
  • Romanian [Auto-generated]
  • Spanish [Auto-generated]
  • Thai [Auto-generated]
Current price: $121.99 Original price: $174.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 7 hours on-demand video
  • 14 articles
  • 3 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Get up & running with Excel's game changing 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
Requirements
  • IMPORTANT: You need a version of Excel that is compatible with Power Pivot (Excel 2013/2016/2019 Standalone, Office 365, Enterprise E3/E5, Office Professional 2016, etc.)
  • This course is designed for PC users (Power Pivot is currently NOT available for Mac)
  • Experience with Excel PivotTables and formulas & functions is strongly recommended
Description

Hear why this is one of the TOP-RATED Excel courses on Udemy, and the #1 Excel Power Query + Excel Power Pivot course:

"I am a self-taught Excel Power Query user and it took me a while to understand what each tool does and how it interacts with others. Thanks to your introduction I finally nailed it in a very clear, unambiguous way. You helped me build a method that I can confidently apply to my data in Excel. Thank you so much!"

-Francesca C.

"I'm less of an expert at breathing than Chris is at Excel. This course is thorough and well-planned, and he presents in a manner that simplifies the complicated. Well worth your time if you want to master Excel power query and power pivot!"

-Tim B.

"I'm geeking out, this is so cool. Where has this been all my life???"

-Karen P.

__________

COURSE DESCRIPTION:

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 Excel analytics game, this course is the A-Z guide that you're looking for.

I'll introduce the "Power Excel" landscape, and explore what these Excel tools are all about and why they are changing the world of self-service business intelligence. Together, we'll walk through the Excel BI workflow, and build an entire Excel data model from scratch:

  • First we'll get hands-on with Power Query; a tool to extract, transform, and load data into Excel from flat files, folders, databases, API services and more. We'll practice shaping, blending and exploring our project files in Excel's query editor, and create completely automated loading procedures inside of Excel 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 the Excel data model interface, introduce some best practices and pro tips, and then create our own relational database to analyze throughout the course.

  • Finally, we'll use Power Pivot and DAX to explore and analyze our Excel data model. Unlike traditional Excel Pivot Tables, Power Pivot allows you to analyze hundreds of millions of rows across multiple data tables (inside of Excel!), 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.

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 excel analytics NINJA.

See you in there!

-Chris (Founder, Excel Maven Maven Analytics)

__________

IMPORTANT: Power Query and Power Pivot are currently NOT available in Excel for Mac. You'll need a PC version of Excel that is compatible with Power Pivot (Excel 2010 with plug-in, Excel 2013, Excel 2016, or Excel 2019 Standalone, Office 365 Pro Plus, Enterprise E3/E5, Office Professional 2016, etc.)

__________

Most students in this course enroll in our full EXCEL LEARNING PATH, designed to help you build a deep, expert-level Excel skill set:

  1. Advanced Excel Formulas & Functions

  2. Data Visualization with Excel Charts & Graphs

  3. Data Analysis with Excel PivotTables

  4. Excel Power Query, Power Pivot & DAX (you are here)

  5. Excel Pro Tips for Power Users

Looking for the full business intelligence stack? Search for "Maven Analytics" to browse our full course library, including Excel, Power BI, MySQL, and Tableau courses!

*** This course includes Excel project files, quizzes & homework exercises, 1-on-1 instructor support, LIFETIME access and a 100% money-back guarantee! ***

Who this course is for:
  • Excel users who want to learn advanced data modeling & business intelligence tools
  • Students looking for a comprehensive, engaging, and highly interactive approach to Excel training
  • Anyone looking to become an Excel POWER USER and supercharge their analytics skillset
  • Students looking to pursue a career in data analysis or business intelligence
Course content
Expand all 88 lectures 07:15:07
+ Getting Started
7 lectures 11:28

Walkthrough to cover exactly how the course is structured and what you need to know before diving into Power Query, Power Pivot and DAX tools.

Preview 01:47

Before getting started with the course, it's important to make sure that you are using a version of Excel that is compatible with Power Pivot. Check the Microsoft Office Support website to make sure that you are using a proper version.

Preview 03:15
READ ME: Important Notes for New Students
01:54

In this lecture I'll introduce our course project, outline the downloadable files that we'll be working with, and demonstrate exactly how to access them from the course dashboard.

Introducing the Course Project
01:16
DOWNLOAD: Course Resources
00:08

To wrap up the intro section, I'll explain exactly what to expect (and what NOT to expect) from the course.

Setting Expectations
01:54
Looking to become an Excel MASTER?
01:13
+ Intro to “Power Excel"
4 lectures 13:13

This lecture is all about outlining the "Power Excel" landscape. I'll show you exactly how these new data modeling and business intelligence tools will fit into your workflow, from Power Query to Power Pivot and DAX.

Understanding the “Power Excel” Workflow
03:50

In this lecture I'll explain why Power Query and Power Pivot are so awesome that they've been called the "best thing to happen to Excel in 20 years" from industry experts. We'll talk about the ability to load hundreds of millions of rows, build data models to blend data across sources, automate your data loading and ETL process, and create powerful calculated fields using data analysis expressions (DAX).

Preview 08:18

This lecture will help you understand when and why to use Excel Power Query and Power Pivot (i.e. when you are dealing with very large data sets, need to blend data across multiple tables, etc.)

When to use Power Query & Power Pivot
00:44
QUIZ: Intro to "Power Excel"
7 questions
HOMEWORK: Intro to "Power Excel"
00:21
+ Connecting & Transforming Data with Power Query in Excel
19 lectures 01:42:53
Introduction
00:27

In this lecture we'll introduce Power Query, which we'll connect and transform data from raw sources, edit it using the Query Editor, and load it straight into Excel.

Getting to Know Power Query in Excel
02:40

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.

Preview 03:17

In this lecture we'll explore Excel's data loading options from Power Query, and talk about the difference between loading tables and only generating connections.

Power Query Data Loading Options
01:40
IMPORTANT: Updating Locale Settings
00:46

This lecture will cover some of the most common data transformation tools in Excel's Query Editor, such as adding or removing columns or rows, changing data types, etc.

Applying Basic Table Transformations with Power Query
08:06

In this lecture we'll cover Power Query tools designed specifically to work with text in Excel, such as merging or splitting columns, extracting characters, calculating string lengths, etc.

Power Query Demo: Text Tools
10:17

In this lecture we'll review Power Query tools designed specifically to work with numbers or data fields in Excel, such as returning aggregated values like sums or averages, creating new calculated columns, rounding numbers, etc.

Power Query Demo: Number & Value Tools
07:41

In this lecture we'll cover Power Query tools specifically designed to work with date and time fields in Excel, like calculating months, weeks, weekdays, quarters, etc.

Power Query Demo: Date & Time Tools
07:39

In this lecture I'll quickly demonstrate how you can use custom M queries in the Excel query editor to build a rolling calendar that will always update with dates through the current day.

PRO TIP: Creating a Rolling Calendar with Power Query
05:02

This lecture will show you how to create new unique identifiers using index columns in the Excel Query Editor, as well as calculated fields based on custom user-defined conditions.

Power Query Demo: Generating Index & Conditional Columns
07:44

In this lecture we'll practice using "group by" tools in the Excel Query Editor to aggregate or roll up raw data to new levels of granularity.

Power Query Demo: Grouping & Aggregating Records
07:10

This lecture demystifies the concept of "pivoting" or "unpivoting" a data table. I'll show you exactly what these tools do, and demonstrate with a sample table in the Excel Query Editor.

Preview 08:52

In this lecture, I'll show you how you can access, edit, and delete existing Excel workbook queries.

Modifying Excel Workbook Queries
04:18

This lecture demonstrates how to merge Excel Power Query queries together to pull fields from one table into another based on common fields or "keys".

Merging Queries with Power Query
07:43

In this lecture, I'll show you how to append or "stack" data from multiple tables that share the same column structure and data types, using Excel's Power Query tools.

Appending Queries with Power Query
05:49

In this lecture I'll demonstrate how to connect to an entire folder and automatically append all of the files within in (including new files as they are added), and load to Excel.

Power Query Demo: Connecting to a Folder of Files
09:32

In this lecture we'll review my personal favorite best practices for using Excel Power Query.

Excel Power Query Best Practices
02:44
QUIZ: Connecting & Transforming Data with Power Query in Excel
10 questions
HOMEWORK: Connecting & Transforming Data with Power Query in Excel
01:25
+ Building Table Relationships with Excel's Data Model
16 lectures 59:45
Introduction
00:28

In this lecture I'll introduce Excel's "Data Model", which is where large data files can be compressed and modeled together using table relationships.

Meet Excel's "Data Model"
03:38

This lecture outlines the two views within the Excel data model: Data view and Diagram view. Data view allows you to access the data within tables (organized as tabs), and diagram view allows you to create and view your table relationships and overall data model.

The Data Model Data vs. Diagram View
02:33

This lecture covers one of the most important topics in the course: database normalization. Normalization is all about structuring tables to create efficient and effective data models in Excel.

Principles of Database Normalization
04:15

In this lecture we'll compare and contrast the two primary types of tables in an Excel data model: data (or "fact") tables and lookup (or "dimension") tables.

Understanding Data Tables vs. Lookup Tables
05:40

In this lecture I'll explain the difference between manually merging fields from multiple tables and creating relationships to connect them using Excel's data model (which is much more efficient!)

Benefits of Relationships vs. Merged Tables
02:07

In this lecture we'll use the Excel data model diagram view to create our first table relationships.

Creating Table Relationships in Excel's Data Model
06:55

In this lecture I'll demonstrate how to modify or edit existing table relationships in the Excel data model diagram view, using several different methods.

Modifying Data Model Table Relationships
02:06

In this lecture I'll demonstrate how to create multiple relationships against the same key, and how to determine which relationship is active vs. inactive in an Excel data model.

Managing Active vs. Inactive Table Relationships
03:55

This lecture explores the concept of cardinality, and demonstrates the differences between 1-to-1, 1-to-many, and many-to-many relationships. I'll also show you exactly why 1-to-many relationships are critical when it comes to building normalized data models in Excel.

Preview 05:46

In this lecture I'll demonstrate exactly how to build an Excel model containing multiple data tables. Rather than connect those tables together, we'll connect them indirectly via relationships to shared lookup tables.

Connecting Multiple Data Tables in the Data Model
05:59

This lecture demonstrates the importance of filter direction within Excel's data model, and explains the concept of filter context flowing "downstream" to related tables.

Understanding Filter Flow
05:08

In this lecture, we'll talk about when, why, and how to hide fields from Excel client tools such as PivotTables, using a number of different methods.

Hiding Fields from Excel Client Tools
04:59

In this lecture we'll practice creating hierarchies within Excel's data model, which are new fields containing groups or sets of related fields (such as country, state, and city).

Defining Hierarchies in a Data Model
03:54

In this lecture I'll outline my personal favorite Excel data model best practices.

Excel Data Model Best Practices
01:38
QUIZ: Building Table Relationships with Excel's Data Model
10 questions
HOMEWORK: Building Table Relationships with Excel's Data Model
00:44
+ Analyzing Data with Power Pivot & DAX
14 lectures 01:04:56
Introduction
00:29

In this lecture I'll introduce Excel Power Pivot and outline some of the key benefits.

Creating a "Power" Pivot Table
05:27

In this lecture I'll explore the similarities and differences between regular Excel PivotTables and "Power" PivotTables, which connect to entire data models.

Preview 02:48

In this lecture I'll introduce the formula language that enables you to create powerful calculated fields from a data model in Excel: Data Analysis Expressions (aka "DAX").

Introducing Data Analysis Expressions (DAX)
01:12

In this lecture I'll introduce the first method of using DAX to create new calculated fields: calculated columns. I'll showcase some "good" and "bad" examples, and demonstrate how they can be created within the Excel data model window.

Understanding DAX Calculated Columns
08:39

In this lecture I'll introduce the second method of using DAX to create new calculated fields: measures. I'll explain how they can be used and why they are so powerful, especially when compared to traditional Excel PivotTable calculated fields. 

Understanding DAX Measures
02:24

In this lecture I'll briefly introduce implicit measures, which are measures that are automatically created by Excel when you drag a field in the PivotTable field list.

Creating Implicit DAX Measures
01:59

In this lecture I'll demonstrate how to use the data model's "AutoSum" feature to quickly create basic measures using common functions in Excel (SUM, COUNT, AVERAGE, etc).

Creating Explicit DAX Measures with AutoSum
07:08

In this lecture I'll introduce the most powerful means of creating measures: building calculated explicit measures using the Power Pivot dialog box in Excel. This is where you can use complex combinations of DAX functions to create incredibly powerful and flexible measures.

Creating Explicit DAX Measures with Power Pivot
07:42

This lecture introduces the concept of filter context, which is the set of filters passed by the Excel PivotTable layout. Understanding filter context is critical to understanding how measures are calculated.

Understanding DAX Filter Context
09:18

In this lecture, I'll guide you through the exact steps that Excel takes behind the scenes to calculate each cell containing a measure. This demonstration will be critical to troubleshooting calculation errors and understanding precisely how measures work.

Step-by-Step DAX Measure Calculation
12:06

In this lecture I'll recap the similarities and differences between calculated columns and DAX measures, and explain when to use one approach vs the other in Excel.

RECAP: Calculated Columns vs. DAX Measures
02:29

In this lecture I'll outline some of my personal favorite Excel Power Pivot and DAX best practices.

Excel Power Pivot & DAX Best Practices
01:50
QUIZ: Analyzing Data with Power Pivot & DAX
10 questions
HOMEWORK: Analyzing Data with Power Pivot & DAX
01:25
+ Common DAX Formulas & Functions
24 lectures 02:50:39
Introduction
00:27

In this lecture I'll walk through DAX formula syntax and outline the most common types of operators.

Understanding DAX Formula Syntax & Operators
04:05

In this lecture I'll outline some of the most common DAX categories (Math & Stats, Logical, Text, Filter, and Date & Time), and compare them against traditional Excel formulas.

Common DAX Function Categories
03:54

In this lecture I'll introduce and demonstrate several common math and statistics functions in DAX, such as SUM, DIVIDE, MAX, MIN, and AVERAGE.

DAX Demo: Basic Math & Stats Functions
12:45

In this lecture I'll demonstrate how to use a variety of COUNT functions in DAX, including COUNT, COUNTA, COUNTROWS, and DISTINCTCOUNT.

DAX Demo: COUNT, COUNTA, DISTINCTCOUNT & COUNTROWS
10:35
HOMEWORK: Math & Stats Functions
01:10

In this lecture I'll introduce and demonstrate several common logical functions in DAX, including IF, IFERROR, AND, OR, etc.

DAX Demo: Logical Functions (IF/AND/OR)
16:49

In this lecture I'll introduce the SWITCH function in DAX, and demonstrate how it can be combined with TRUE to eliminate the need for nested IF statements to test multiple criteria in Excel.

DAX Demo: SWITCH & SWITCH(TRUE)
10:33

In this lecture I'll introduce and demonstrate a number of text-specific functions in DAX, including LEN, CONCATENATE, UPPER/LOWER/PROPER, LEFT/MID/RIGHT, SEARCH, and SUBSTITUTE.

DAX Demo: Common Text Functions
10:53
HOMEWORK: Logical & Text Functions
00:43

In this lecture I'll introduce arguably the most powerful DAX function of all: CALCULATE. I'll explain exactly how this function can be used in Excel, and demonstrate several examples applied to our course project files.

DAX Demo: CALCULATE
14:39

In this lecture I'll demonstrate how to add a FILTER function within CALCULATE to create new filter context in DAX. 

DAX Demo: Adding Filter Context with FILTER (Part 1)
09:20

In this lecture we'll revisit the use of FILTER within a CALCULATE function, and illustrate exactly how the FILTER function impacts the way measures are calculated behind the scenes. 

DAX Demo: Adding Filter Context with FILTER (Part 2)
14:23

In this lecture I'll explain how to use the ALL function to remove filter context within a PivotTable, and demonstrate how it is commonly used for "% of whole" calculations defined by DAX measures.

DAX Demo: Removing Filter Context with ALL
09:06
HOMEWORK: CALCULATE, FILTER & ALL
01:21

In this lecture I'll show you how to use the RELATED function to create new calculated columns that retrieve values from related tables in the Excel data model (just like a VLOOKUP!).

DAX Demo: Joining Data with RELATED
03:39

In this lecture I'll introduce you to iterator, or "X" functions, which operate by repeating a calculation across all rows in a table and aggregating the results. In this demo we'll look at the SUMX function specifically, and compare it against the traditional Excel SUMPRODUCT function.

DAX Demo: Iterating with SUMX
12:20

In this lecture we'll take a look at the RANKX iterator function, which allows you to calculate an item's rank based on a given set of conditions or criteria.

DAX Demo: Iterating with RANKX
05:33
HOMEWORK: Iterator ("X") Functions
00:53

In this lecture we'll review the most common date and time DAX functions, including DAY/MONTH/YEAR, HOUR/MINUTE/SECOND, TODAY/NOW, WEEKDAY/WEEKNUM, EOMONTH and DATEDIFF.

DAX Demo: Basic Date & Time Functions
04:58

In this lecture we'll introduce DAX's powerful time intelligence formulas, and demonstrate how to use them to measure performance-to-date, make period-over-period comparisons, and calculate running totals and moving averages.

Preview 17:02
HOMEWORK: Time Intelligence
00:25

In this lecture I'll quickly review some of the key considerations when it comes to Excel data model performance and speed, including the use of slicers, iterator functions, and redundant columns.

DAX Speed & Performance Considerations
03:02

In this lecture I'll outline some of my personal favorite DAX best practices.

DAX Best Practices
02:03
QUIZ: Common DAX Formulas & Functions
12 questions
+ Wrapping Up
4 lectures 12:11
Introduction
00:53

In this lecture I'll quickly outline a few common options when it comes to visualizing data from your Excel data model, including PivotTables and PivotCharts, Power View, CUBE functions, and Microsoft Power BI.

Data Visualization Options in Excel
04:35

In this lecture I'll provide a quick sneak peek into Microsoft Power BI, a standalone application built on the same exact tools covered in this course (Power Query, Power Pivot and DAX).

Preview 05:55
More from Maven Analytics
00:48