Microsoft Excel Power Query Power Pivot Dax

Learn advanced Excel for data analysis & business intelligence (Power Query, Power Pivot & DAX language. Excel 2013+)

Last updated 2022-01-10 | 4.7

- 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

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

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.)

__________

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!

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.

*** 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

7 sections • 92 lectures

Getting Started 6 lectures

Course Structure & Outline Preview 01:47

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.

IMPORTANT: Versions & Compatibility Preview 03:15

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.

READ ME: Important Notes for New Students Preview 02:13

Introducing the Course Project Preview 01:16

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.

DOWNLOAD: Course Resources Preview 00:16

Setting Expectations Preview 01:54

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

Intro to “Power Excel" 5 lectures

Understanding the “Power Excel” Workflow Preview 03:50

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.

Power Query + Power Pivot: "Best Thing to Happen to Excel in 20 Years" Preview 08:18

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).

When to use Power Query & Power Pivot Preview 00:44

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.)

QUIZ: Intro to "Power Excel"

HOMEWORK: Intro to "Power Excel" Preview 00:21

Connecting & Transforming Data with Power Query in Excel 20 lectures

Introduction Preview 00:27

Getting to Know Power Query in Excel Preview 02:40

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.

Exploring Excel's Power Query Editor Preview 03:17

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 Preview 01:40

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.

IMPORTANT: Updating Locale Settings Preview 00:46

Applying Basic Table Transformations with Power Query Preview 08:06

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.

Power Query Demo: Text Tools Preview 10:17

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: Number & Value Tools Preview 07:41

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: Date & Time Tools Preview 07:39

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.

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

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.

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

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: Grouping & Aggregating Records Preview 07:10

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: Pivoting & Unpivoting Data Preview 08:52

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.

Modifying Excel Workbook Queries Preview 04:18

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

Merging Queries with Power Query Preview 07:43

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".

Appending Queries with Power Query Preview 05:49

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.

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

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.

Excel Power Query Best Practices Preview 02:44

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

QUIZ: Connecting & Transforming Data with Power Query in Excel

HOMEWORK: Connecting & Transforming Data with Power Query in Excel Preview 01:19

Building Table Relationships with Excel's Data Model 17 lectures

Introduction Preview 00:28

Meet Excel's "Data Model" Preview 03:38

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.

The Data Model Data vs. Diagram View Preview 02:33

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.

Principles of Database Normalization Preview 04:15

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.

Understanding Data Tables vs. Lookup Tables Preview 05:40

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.

Benefits of Relationships vs. Merged Tables Preview 02:07

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!)

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

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

Modifying Data Model Table Relationships Preview 02:06

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.

Managing Active vs. Inactive Table Relationships Preview 03:55

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.

Understanding Relationship Cardinality Preview 05:46

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.

Connecting Multiple Data Tables in the Data Model Preview 05:59

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.

Understanding Filter Flow Preview 05:08

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.

Hiding Fields from Excel Client Tools Preview 04:59

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.

Defining Hierarchies in a Data Model Preview 03:54

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).

Excel Data Model Best Practices Preview 01:38

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

QUIZ: Building Table Relationships with Excel's Data Model

HOMEWORK: Building Table Relationships with Excel's Data Model Preview 00:39

Analyzing Data with Power Pivot & DAX 15 lectures

Introduction Preview 00:29

Creating a "Power" Pivot Table Preview 05:27

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

Power Pivots vs. “Normal” Pivots in Excel Preview 02:48

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

Introducing Data Analysis Expressions (DAX) Preview 01:12

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").

Understanding DAX Calculated Columns Preview 08:39

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 Measures Preview 02:24

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.

Creating Implicit DAX Measures Preview 01:59

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 Explicit DAX Measures with AutoSum Preview 07:08

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 Power Pivot Preview 07:42

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.

Understanding DAX Filter Context Preview 09:18

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.

Step-by-Step DAX Measure Calculation Preview 12:06

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.

RECAP: Calculated Columns vs. DAX Measures Preview 02:29

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.

Excel Power Pivot & DAX Best Practices Preview 01:50

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

QUIZ: Analyzing Data with Power Pivot & DAX

HOMEWORK: Analyzing Data with Power Pivot & DAX Preview 01:22

Common DAX Formulas & Functions 25 lectures

Introduction Preview 00:27

Understanding DAX Formula Syntax & Operators Preview 04:05

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

Common DAX Function Categories Preview 03:54

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.

DAX Demo: Basic Math & Stats Functions Preview 12:45

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: COUNT, COUNTA, DISTINCTCOUNT & COUNTROWS Preview 10:35

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

HOMEWORK: Math & Stats Functions Preview 01:07

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

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

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

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: Common Text Functions Preview 10:53

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.

HOMEWORK: Logical & Text Functions Preview 00:36

DAX Demo: CALCULATE Preview 14:39

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: Adding Filter Context with FILTER (Part 1) Preview 09:20

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 2) Preview 14:23

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: Removing Filter Context with ALL Preview 09:06

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.

HOMEWORK: CALCULATE, FILTER & ALL Preview 01:19

DAX Demo: Joining Data with RELATED Preview 03:39

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: Iterating with SUMX Preview 12:20

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 RANKX Preview 05:33

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.

HOMEWORK: Iterator ("X") Functions Preview 00:48

DAX Demo: Basic Date & Time Functions Preview 04:58

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: Time Intelligence Formulas Preview 17:02

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.

HOMEWORK: Time Intelligence Preview 00:29

DAX Speed & Performance Considerations Preview 03:02

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 Best Practices Preview 02:03

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

QUIZ: Common DAX Formulas & Functions

Wrapping Up 4 lectures

Introduction Preview 00:53

Data Visualization Options in Excel Preview 04:35

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.

Sneak Peek: Microsoft Power BI Preview 05:55

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).