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 Files | 0:27 |
Power Query formulas | 0:40 |
Discovering Power Query formulas | 2:20 |
Direct Link to MSDN (formula resource) | 3:15 |
Power Query formula "Gotchas" | 3:40 |
Useful Type Conversion Formulas | 5:54 |
Examples | 7:00 |
LEFT error | 7:50 |
Using MSDN library to find formulas | 8:50 |
Example: Text.Start | 10:00 |
Example: Text.PositionOf | 10:25 |
Example: Text.PositionOf "base 1" | 11:30 |
Example: Merge Text with & | 12:25 |
Example: Math error | 13:05 |
Example: Number.From | 14:05 |
420: Power Query Formulas - Date Functions
Keyword | Video Location |
Chapter Files | 0:21 |
Date Function Equivalents - Returning text | 0:35 |
Date Function Equivalents - Returning values | 2:29 |
Date Function Equivalents - Adding periods | 4:09 |
Tip: Use Date.From to convert to a date | 6:45 |
Examples: Month, Days In Month | 7: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 Files | 0:15 |
IF tests using Conditional Columns | 0:25 |
Example In Excel | 2:00 |
Add Column > Conditional Column | 3:30 |
Fill Down | 5:10 |
Manual IF test using Power Query | 5:55 |
try (error tests) | 7:55 |
Example In Excel - Overtime Hours | 9:20 |
Adding the if error statement | 12:00 |
440: Custom Functions
Keyword | Video Location |
Chapter Files | 0:22 |
The Magic of Custom Functions | 0:52 |
Converting a Query Into a Function | 2:12 |
Syntax ()=> | 2:30 |
Debugging Custom Functions | 5:00 |
Example In Excel | 6:45 |
Duplicate | 7:02 |
View > Advanced Editor | 7:29 |
Overview of M code syntax | 7:40 |
Convert M code to a function | 9:20 |
Comment line using // | 10:42 |
Example: How to apply the function | 13:30 |
Pro Tip: Protecting files from folder results | 13:39 |
Pro Tip: Use CTRL to multi-select columns and control order | 15:00 |
Invoke Function using Custom Column | 15:35 |
Pro Tip: Change Type in last | 17:00 |
510: Dynamic Calendar - Base
Keyword | Video Location |
Chapter Files | 0:40 |
Why Use Dynamic Calendar Tables | 0:57 |
Creating a base calendar for Excel solutions | 3:16 |
Example In Excel | 5:13 |
Use fx to Add Step | 6:40 |
List from Start Date to End Date | 8:00 |
Convert List To Table | 8:45 |
Creating a base calendar for Power BI Desktop based solutions | 10:05 |
Example In Power BI Desktop | 12:00 |
511: Dynamic Calendar Table - Standard
Keyword | Video Location |
Chapter Files | 0:20 |
Getting started | 0:30 |
Example In Excel | 1:15 |
Date.ToText([DateKey], "MMM") | 2:46 |
Date.ToText([DateKey], "ddd") | 3:25 |
Non-standard year ends | 4:05 |
Example In Excel | 8:10 |
512: 445 Calendar Table - Part 1 - Period ID Columns
Keyword | Video Location |
Chapter Files | 1:25 |
Getting started | 1:35 |
MonthID and WeekID | 3:25 |
Example In Excel | 4:50 |
Add Column > Add Index Column | 5:55 |
Add Custom Column WeekID | 8:00 |
Pro Tip: Applied Steps > Rename Steps | 9:30 |
Calculating fiscal year | 10:00 |
Example In Excel - Fiscal Year | 12:02 |
513: 445 Calendar Table - Part 2 - x of y Columns
Keyword | Video Location |
Chapter Files | 0:03 |
X of Year columns | 0:15 |
Example In Excel | 1:43 |
Standard > Modulo Day Of Year | 1:50 |
Standard > Modulo Month Of Year | 2:45 |
Standard > Modulo Week Of Year | 3:15 |
Quarter Of Year | 3:37 |
X of Quarter columns | 4:55 |
Example In Excel | 5:40 |
X of Month columns | 7:35 |
Example In Excel | 9:17 |
X of Week columns | 11:11 |
514: 445 Calendar Table - Part 3 - Days in Start End of x
Keyword | Video Location |
Chapter Files | 0:03 |
Days in x columns | 0:15 |
Example In Excel | 1:17 |
Days In Month using if | 2:15 |
Start/End dates | 3:09 |
Example In Excel | 5:09 |
Start Of Month formula | 7:00 |
Start Of Quarter formula | 7:35 |
Start Of Year formula | 8:00 |
End Of Month formula | 9:23 |
End Of Quarter formula | 9:45 |
End Of Year formula | 10:15 |
515: 445 Calendar Table - Part 4 - Variants
Keyword | Video Location |
Getting started | 0:25 |
PeriodID columns | 0:45 |
Fiscal Year column | 1:30 |
X of Year columns | 2:02 |
X of Quarter columns | 2:17 |
X of Month columns | 2:30 |
X of Week columns | 3:03 |
Days in x columns | 3:17 |
Start/End date columns | 3:42 |
550: Data Samples
Keyword | Video Location |
Chapter Files | 0:23 |
The Business Problem: Actual & Forecast | 0:30 |
The Challenge: Many forecast tables | 1:41 |
Transformation steps to transform the forecast table | 4:20 |
Duplicate to use as a function | 5:45 |
Apply the function | 6:40 |
Data model in Power Pivot | 8:20 |
Created relationships | 9:03 |
DAX measures | 9:32 |
DAX measure: Forecast | 10:00 |
DAX measure: Forecast (To End Of Year) measure | 10:20 |
DAX measure: measure | 10:50 |
Key takeaways | 12:09 |
560: Dealing with Over-Denormalized Tables
Keyword | Video Location |
Chapter Files | 0:24 |
Traditional PT vs PT with Power Query | 0:36 |
Normalization: Remove Redundancy | 1:30 |
Normalization Defined | 2:20 |
How a Traditional Pivot Table source looks like | 3:32 |
Denormalization: Add Redundancy | 4:10 |
Example In Excel 2016 - Overview of data | 5:46 |
Using Power Query to Denormalize the data | 7:25 |
Reference Initial Query to create new Query | 8:59 |
Pro Tip: Create Query Groups | 9:16 |
Collapse Pane | 10:02 |
Pro Tip: Use Integers for Relationships | 10:20 |
Pro Tip: Home > Choose Columns button to select columns | 11:05 |
Remove Duplicates | 11:36 |
Excel 2016 - Close and Load To | 12:35 |
Manage Data Model window | 14:10 |
Create the Many To One Relationship in Diagram View | 14:28 |
Create a Pivot Table | 14:50 |