Menu

Comprehensive Power Query Video Series Course Curriculum

Learn Power Query at Your Own Pace!

This self-paced course is designed to build your Power Query skills! Learn how to Extract, Load and Transform your data using Power Query in Excel versions 2010, 2013, 2016 and PBI.

Already enrolled? Log in here.

Introduction: Power Query

Keyword Video Location
You are a (Data) Wizard! (00:15) 0:55
The Information Worker Chronicles (00:45) 1:10
Excel Hell (00:44) 1:55
A global Problem (00:23) 2:39
A lot of work takes place (A LOT!) (01:23) 3:02
What is Power Query and Why (02:25) 4:25
The ETL tool for Excel (00:38) 6:50
The Full Picture (Power BI for Excel) (01:51) 7:28

110: Connecting to Excel Data

Keyword Video Location
Chapter Files (00:22)0:24
Data from an Excel Table (04:29)0:46
Data from ad-hoc ranges (03:04)5:15
Data from a Named range (06:59)8:19

121: Append Data from Flat Files

Keyword Video Location
Chapter Files (03:10)0:25
Demo in Excel 2016 (01:41)3:35
Change Type With Locale (02:29)5:16
Replace Values... (04:20)7:45
Combine Queries > Append (04:10)12:05
Home > Append Queries Button (01:13)16:15

122: Append Data from Folder

Keyword Video Location
Chapter Files (01:25)0:10
Importing a folder of TXT file (04:19)1:35
Demo in Power BI Desktop (01:37)5:54
How the Sample Transform Works (00:19)7:31
Transform to lowercase (00:17)7:50
Filter Rows - Manual Text Filter (00:38)8:07
Reference (Best Practice) (00:37)8:45
Enable load (00:45)9:22
Double Down button (01:15)10:07
Transform Sample File from Data (00:59)11:22
Using Locale (02:19)12:21
Use Gear to Insert Step (01:44)14:40
Refresh (to include Sub-Folder data) (00:46)16:24
Refresh Preview Button (00:20)17:10
How the Sample Transform Works (01:20)17:30
Demo in Excel (01:10)18:50
Source{0}[Content] Fix (01:57)20:00

123: Append Excel Data In Current Workbook

Keyword Video Location
Chapter Files (00:58)0:45
Excel.CurrentWorkbook() (00:27)1:43
Demo in Excel 2013 (01:12)2:10
From Other Sources > Blank Query (00:43)3:22
Formula Bar Checkbox (00:10)4:05
Excel.CurrentWorkbook() (00:34)4:15
Preview Table Data (00:26)4:49
Expand Table Content (01:27)5:15
Troubleshooting Example when using Excel.CurrentWorkbook() (01:35)6:42
Proper Date Tip: Replace "_" with " 1 " (01:03)8:17
Remove Errors (00:53)9:20
Transform > Date > End Of Month (02:05)10:13

124: Append Excel Data From Other Workbooks

Keyword Video Location
Chapter Files (01:26)0:07
Demo In Excel 2016 (00:47)1:33
Filter Rows Begin With (00:35)2:20
Double Down Button (02:15)2:55
Tip: Filter To Table Only (02:05)5:10
Classic Method (02:32)7:15

130: Workflow in Power Query

Keyword Video Location
Example Files (00:04)0:06
Applied Steps Section (01:22)0:10
Power Query Workflow (03:21)1:32
The Step Setting Icon (01:19)4:53
Demo In Excel - Refresh Error (00:48)6:12
DataSource.Error (00:45)7:00
Go To Error (01:25)7:45

141: Web Data

Keyword Video Location
Chapter Files (00:25)0:11
Getting data from the Web (01:04)0:36
Example Web Site (00:30)1:40
Demo In Excel 2013: Power Query > From Web (00:26)2:10
Table Text (01:06)2:36
Power Query looks for table id tags (01:49)3:42
Text File From Web URL (02:02)5:31

142: Importing Data from Exchange

Keyword Video Location
Connecting to Microsoft Exchange (01:55)1:26
Potential Use Cases (01:56)3:21
Demo In Excel 2016 (02:15)5:17
What's Useful To Me (01:50)7:32
Copy From Preview Pane (01:54)9:22
Analyze Findings With Pivot Chart (02:02)11:16
Best Practice (03:58)13:18

143: Databases

Keyword Video Location
Chapter Files - SQL Server Connection (00:24)0:10
Connecting to Databases (01:03)0:34
Connecting to a SQL Azure Database (00:38)1:37
Example in Excel 2013 (03:36)2:15
Expand to add Relationship Columns (00:44)5:51
Relationship Value or Table (03:05)6:35
Advanced Options - Disable Relationships (02:24)9:40
Connecting to a SQL Server (01:03)12:04
Query Folding, Query Optimization & Privacy (02:00)13:07
Demo In Excel Data Source Settings (03:18)15:07

144: Data From SharePoint

Keyword Video Location
SharePoint Data (01:29)0:34
Demo in Power BI Desktop (02:21)2:03
Organizational account (02:25)4:24
Connect to a SharePoint Folder (02:03)6:49
Tip: SharePoint.Content () (01:50)8:52
Double Down Button (02:15)10:42
Transform > Fill Down (00:28)12:57
Unpivot Other Columns (00:46)13:25
Merge Queries (01:45) 14:11
Power BI Visuals (01:43)15:56
Conclusions (01:03)17:39

210: Loading Data

Keyword Video Location
Chapter Files (00:04)0:20
Load Destinations (01:46)0:24
Load To Power Pivot (01:43)2:10
Changing the default behavior (02:31)3:53
Demo In Excel 2016 (01:10)6:24
Load To Connection Only (00:41)7:34
Append (00:39)8:15
Load To Table (00:11)8:54
Load To Data Model (00:57)9:05
Change Load To behavior (00:52)10:02
Change to Data Model Structure (01:58)10:54

220: Load Issues

Keyword Video Location
Chapter Files (00:05)0:14
The importance of defining data types (02:54)0:19
Demo in Excel 2013 (02:13)3:13
Troubleshooting dates formatted as numbers (00:48)5:26
Add To Data Model changes Any data type (02:17)6:14
Best Practice: Define data types in last query (03:50)8:31

310: Transforming a non-delimited text file

Keyword Video Location
Chapter Files (00:27)0:26
Importing and cleaning non-delimited files (03:20)0:53
Sample Raw Source Data (01:07)4:13
Demo in Power BI Desktop (01:10)5:20
Remove Rows (00:24)6:30
Transform > Trim (00:35)6:54
Split Column > by Number of Characters (01:24)7:29
Use First Rows As Headers (01:07)8:53
Dealing With Errors (00:35)10:00
Home > Remove Errors (00:21)10:35
Deciding a Best Approach (01:54)10:56
Pro Tip: Filter Columns (00:34)12:50
Merge Columns (00:53)13:24
Split Column > by Delimiter (02:22)14:17
Add Column > Date > Year (01:18)16:39
Gear Icon - Change Source Files (01:28)17:57

320: Grouping & Sorting

Keyword Video Location
Chapter Files (00:06)0:07
Summarizing or Grouping Data (01:49)0:13
The Example (01:05)2:02
Demo In Excel 2016 (00:46)3:07
Replace Current Step Message (02:07)3:53
Home > Group By Button (01:29)6:00
Sort (01:41)7:29
Merge Columns (01:13)9:10
Adding Conditional Formatting (00:44)10:23
Concepts of Summarizing data (01:31)11:07
Basics of Sorting in Power Query (01:12)12:38

331: Merging Tabular Data - Basics

Keyword Video Location
Chapter Files (00:09)0:24
Merging Tables and Queries (01:14)0:33
Demo in Excel 2013 (02:08)1:47
Merge Table (02:10)3:55
Expand Table Data (00:57)6:05
Merge Table on Different Column (00:49)7:02
Troubleshooting Incorrect Merge (03:21)7:51

332: Merging Tabular Data - Join Types

Keyword Video Location
Chapter Files (00:09)0:31
Join Types (00:38)0:40
Demo in Excel 2016 (02:04)1:18
Merge with a Two Column Join (00:42)3:22
Left Outer Join (02:09)4:04
Right Outer Join (01:23)6:13
Full Outer Join (01:34)7:36
Inner Join (01:44)9:10
Left Anti Join (01:09)10:54
Right Anti Join (01:57)12:03

351: Unpivot

Keyword Video Location
Chapter Files (00:04)0:29
Un-Pivoting pivoted data sets (01:13)0:33
Demo In Excel 2016 (01:09)1:46
Filter Out Rows & Columns Not Needed (00:31)2:55
Right Click > Unpivot Other Columns (01:03)3:26
Insert PivotTable (01:11)4:29
Refresh with End User updates (00:39)5:40
Tip: Refresh Data & Pivot Table (00:54)6:19
It Works - Review of Query Steps after updates (01:07)7:13

352: Unpivoting Stacked Tables

Keyword Video Location
Chapter Files (00:09)0:16
Un-pivoting stacked tables (01:52)0:25
Demo in Power BI Desktop (01:24)2:17
What is the Pattern of the Data (00:44)3:41
Add Column > Add Index Column (00:35)4:25
Add Column > Standard > Modulo (01:04)5:00
Transform > Pivot Column (01:00)6:04
Remove Columns Not Needed (00:26)7:04
Rename Columns (00:38)7:30
Fill Up (00:33)8:08
Remove NULL values (00:19)8:41
QA the Results (01:14)9:00

353: Unpivoting SubcategorizedTables

Keyword Video Location
Chapter Files (00:12)0:15
Transposing Data (01:35)0:27
Un-pivoting Subcategorized Tables (01:43)2:02
Demo In Power BI Desktop (00:35)3:45
Effects of Center Across Selection or Merge Cells (01:13)4:20
Transform > Transpose (00:09)5:33
Transform > Fill Down (00:14)5:42
Tip: Merge Columns with Custom Separator Pipe Character (00:56)5:56
Transform > Transpose (back) (00:16)6:52
Use First Row As Headers (01:07)7:08
Finding the Angle with Filter (00:26)8:15
Right Click Unpivot Other Columns (00:24)8:41
Split Column by Delimiter (00:56)9:05
Add Column > Custom Column Text Date (00:22)10:01
Change Type > Date (01:53)10:23

410: Power Query Formulas - Basics

Keyword Video Location
Chapter Files0:27
Power Query formulas0:40
Discovering Power Query formulas2:20
Direct Link to MSDN (formula resource)3:15
Power Query formula "Gotchas"3:40
Useful Type Conversion Formulas5:54
Examples7:00
LEFT error7:50
Using MSDN library to find formulas8:50
Example: Text.Start10:00
Example: Text.PositionOf10:25
Example: Text.PositionOf "base 1"11:30
Example: Merge Text with &12:25
Example: Math error13:05
Example: Number.From14:05

420: Power Query Formulas - Date Functions

Keyword Video Location
Chapter Files0:21
Date Function Equivalents - Returning text0:35
Date Function Equivalents - Returning values2:29
Date Function Equivalents - Adding periods4:09
Tip: Use Date.From to convert to a date6:45
Examples: Month, Days In Month7:25
Example: Date.ToText([Dates],"MMM")8:50
Date.ToText([Dates],"MMM dd, yyyy")10:00
Date.ToText([Dates],"yyyy-MM-dd"10:35
Date.ToText([Dates], "MMM dd, yyyy (ddd)")11:15
Date.From([Date_Long])11:50
Number.From([Date])12:35
Date.AddMonths([EndOfMonth],1)13:30
Date.AddMonths([EndOfMonth],-1)14:40

430: Formulas - Conditional Logic

Keyword Video Location
Chapter Files0:15
IF tests using Conditional Columns0:25
Example In Excel2:00
Add Column > Conditional Column3:30
Fill Down5:10
Manual IF test using Power Query5:55
try (error tests)7:55
Example In Excel - Overtime Hours9:20
Adding the if error statement12:00

440: Custom Functions

Keyword Video Location
Chapter Files0:22
The Magic of Custom Functions0:52
Converting a Query Into a Function2:12
Syntax ()=>2:30
Debugging Custom Functions5:00
Example In Excel6:45
Duplicate7:02
View > Advanced Editor7:29
Overview of M code syntax7:40
Convert M code to a function9:20
Comment line using //10:42
Example: How to apply the function13:30
Pro Tip: Protecting files from folder results13:39
Pro Tip: Use CTRL to multi-select columns and control order15:00
Invoke Function using Custom Column15:35
Pro Tip: Change Type in last 17:00

510: Dynamic Calendar - Base

Keyword Video Location
Chapter Files0:40
Why Use Dynamic Calendar Tables0:57
Creating a base calendar for Excel solutions3:16
Example In Excel5:13
Use fx to Add Step6:40
List from Start Date to End Date8:00
Convert List To Table8:45
Creating a base calendar for Power BI Desktop based solutions10:05
Example In Power BI Desktop12:00

511: Dynamic Calendar Table - Standard

Keyword Video Location
Chapter Files0:20
Getting started0:30
Example In Excel1:15
Date.ToText([DateKey], "MMM")2:46
Date.ToText([DateKey], "ddd")3:25
Non-standard year ends4:05
Example In Excel8:10

512: 445 Calendar Table - Part 1 - Period ID Columns

Keyword Video Location
Chapter Files1:25
Getting started1:35
MonthID and WeekID3:25
Example In Excel4:50
Add Column > Add Index Column5:55
Add Custom Column WeekID8:00
Pro Tip: Applied Steps > Rename Steps9:30
Calculating fiscal year10:00
Example In Excel - Fiscal Year12:02

513: 445 Calendar Table - Part 2 - x of y Columns

Keyword Video Location
Chapter Files0:03
X of Year columns0:15
Example In Excel1:43
Standard > Modulo Day Of Year1:50
Standard > Modulo Month Of Year2:45
Standard > Modulo Week Of Year3:15
Quarter Of Year3:37
X of Quarter columns4:55
Example In Excel5:40
X of Month columns7:35
Example In Excel9:17
X of Week columns11:11

514: 445 Calendar Table - Part 3 - Days in Start End of x

Keyword Video Location
Chapter Files0:03
Days in x columns0:15
Example In Excel1:17
Days In Month using if2:15
Start/End dates3:09
Example In Excel5:09
Start Of Month formula7:00
Start Of Quarter formula7:35
Start Of Year formula8:00
End Of Month formula9:23
End Of Quarter formula9:45
End Of Year formula10:15

515: 445 Calendar Table - Part 4 - Variants

Keyword Video Location
Getting started0:25
PeriodID columns0:45
Fiscal Year column1:30
X of Year columns2:02
X of Quarter columns2:17
X of Month columns2:30
X of Week columns3:03
Days in x columns3:17
Start/End date columns3:42

550: Data Samples

Keyword Video Location
Chapter Files0:23
The Business Problem: Actual & Forecast0:30
The Challenge: Many forecast tables1:41
Transformation steps to transform the forecast table4:20
Duplicate to use as a function5:45
Apply the function6:40
Data model in Power Pivot8:20
Created relationships9:03
DAX measures9:32
DAX measure: Forecast10:00
DAX measure: Forecast (To End Of Year) measure10:20
DAX measure: measure10:50
Key takeaways12:09

560: Dealing with Over-Denormalized Tables

Keyword Video Location
Chapter Files0:24
Traditional PT vs PT with Power Query0:36
Normalization: Remove Redundancy1:30
Normalization Defined2:20
How a Traditional Pivot Table source looks like3:32
Denormalization: Add Redundancy4:10
Example In Excel 2016 - Overview of data5:46
Using Power Query to Denormalize the data7:25
Reference Initial Query to create new Query8:59
Pro Tip: Create Query Groups9:16
Collapse Pane10:02
Pro Tip: Use Integers for Relationships10:20
Pro Tip: Home > Choose Columns button to select columns11:05
Remove Duplicates11:36
Excel 2016 - Close and Load To12:35
Manage Data Model window14:10
Create the Many To One Relationship in Diagram View14:28
Create a Pivot Table14:50