Introduction
Make your data useful with Power Query
(01:12)
What to know before starting this course
(00:55)
1. What Is Power Query (Get & Transform)?
Power Query example
(06:48)
Differences between Excel and Power Query
(03:55)
2. Working with Queries
Data types explained
(04:42)
Query data from a table or range
(02:09)
Query data from another Excel file
(02:28)
Load data only as a connection
(03:24)
3. Working with Columns
Fill up and fill down
(02:17)
Split column by delimiter
(03:09)
Split into rows
(01:47)
Add conditional and custom columns
(04:51)
Add column by example
(04:31)
Merging columns
(03:38)
Sorting and filtering data in Power Query
(02:44)
4. Working with Formulas
Use IF formulas
(02:01)
Nest IF and AND
(02:04)
AddDays to determine deadline
(02:44)
5. Pivoting and Unpivoting Data
Pivot data in Power Query
(01:29)
Pivot and append data
(02:52)
Pivot—Don't aggregate
(02:01)
Unpivot data in Power Query
(02:08)
6. Grouping
Group data
(01:57)
7. Appending Queries
Two data sets
(02:17)
Multiple tables
(03:18)
Query data from a folder and import multiple files
(03:12)
Combining table with different headers
(02:18)
Append multiple sheets
(02:55)
8. Merging Data with Joins
Overview of joins in Power Query
(03:11)
Full outer join
(04:03)
Inner join
(02:18)
Left anti join
(03:02)
Right anti join
(02:31)
Left outer join
(01:58)
Right outer join
(02:00)
Outer join versus VLOOKUP
(02:54)
Merge with multiple fields
(03:04)
Joins: Left or right
(02:13)
Approximate match equivalent of VLOOKUP: Binning
(02:38)
Approximate match equivalent of VLOOKUP: Conditional column
(02:31)
9. Drill Down to Use Variables
Drill down to create a variable in Power Query
(05:26)
10. Fuzzy Matching
Matching inconsistent entries by percentage
(05:09)
Merging inconsistent data with a transformation table
(04:29)
11. Custom Data Types
Make and use custom data types
(07:48)
Apply Your Learning with Real-World Challenges
Easy real-world challenge 1: Pies and prices
(07:19)
Easy real-world challenge 2: Volunteer hours
(05:08)
Monster real-world challenge: Tracking donations
(08:26)
Ex_Files_Excel_Power_Query_Get_Transform.zip
(609 KB)
Glossary_ExcelPowerQuery_GetTransform.zip
(102 KB)