Power Pivot, Power Query & DAX
New! Subscribe and get access to all over 20 classes for just $49/mo, or purchase this class a la carte
In this advanced Excel course, we look at three crucial advanced Excel features Power Pivot, Power Query, and DAX.
This suite of Excel functions allows you to manipulate, analyze, and evaluate millions of rows of data from Excel or other databases.
We first look at how to bring your data together, merge, and clean it using Power Query before moving onto creating relationships between our data tables and building out a data model. We then look at performing calculations on our data model using DAX, and finally, we look at displaying our analysis using PivotTables and PivotCharts.
If you’ve reached a point where you need Excel to work with data at scale, then these tools will unlock that capability.
This course also includes practice exercises and follow-along files so you can build your data models as we go.
Welcome and Overview
What is Power Query?
What is Power Pivot?
Exploring the Power Query Editor
Common Power Query Transformations
Editing an Existing Query
Import Multiple Files from a Folder
Connect to Data in Another Excel Workbook
IMPORTANT: Checking the Location of your Query's Source
Get Data From the Web
Practise Exercise
Unpivoting Columns
Combine Data from Multiple Tables with Merge Queries
Use Merge Queries to Compare Two Tables
Stack Data into One Table with Append Queries
Duplicating and Referencing Queries
Grouping and Aggregating Data
Conditional Columns in Power Query
Practise Exercise
Enable the Power Pivot Add-In
Understanding the Power Pivot Window
Creating Relationships Between tables
Managing the Relationships of the Model
Creating a PivotTable from the Data Model
Hide Fields from Client Tools
Grouping Queries
Practise Exercise
Why use DAX?
Creating Calculated Columns with DAX
Creating your First DAX Measure
The COUNTROWS Function
SUMX and RELATED Functions
Practise Exercise
Create a Date Table in Power Pivot
The CALCULATE Function
The DIVIDE Function
Using the DATESYTD Function
Calculate the Percentage of a Total
Practise Exercise
Create PivotTables and PivotCharts
Using Slicers with your PivotTables
Create a Top 10 PivotTable
Practise Exercise
Wrap Up
Exercise Files
Instructor Demo Files