Excel for Mac: Automate Data Prep with Power Query
Welcome to the Excel for Mac: Automate Data Prep with Power Query course landing page. Here you’ll find everything you need, including the videos, the practice files and your PDF reference guide.
Download the Practice Files
Download the Practice Files
Download a copy of the practice files and follow along with the demos in real-time.
Download the Reference Guide
Download the Reference Guide
Download the Excel for Mac Power Query Reference Guide in PDF format.
01 Introduction to the Course (04:38)
01 Introduction to the Course (04:38)
An introduction to your instructor and the course ahead
02 Life Without Power Query (04:28)
02 Life Without Power Query (04:28)
A story that illustrates how much time you're wasting when you don't use Power Query
03 Import Data Using Power Query: CSV an...
03 Import Data Using Power Query: CSV and Excel (14:32)
Your first hands-on look at importing data
04 An Introduction to the Query Editor (...
04 An Introduction to the Query Editor (09:27)
A guided tour of where the magic happens
05 The Query Editor: Delete, Rename and ...
05 The Query Editor: Delete, Rename and Move Steps (06:13)
Step Management in the Query Editor
06 The Query Editor: The Built-in Steps ...
06 The Query Editor: The Built-in Steps Explained (08:41)
Understand what Power Query adds automatically and why
07 An Introduction to M (04:04)
07 An Introduction to M (04:04)
A beginner-friendly look at the code behind the scenes
08 Removing Unwanted Rows (14:21)
08 Removing Unwanted Rows (14:21)
Clean up blank, duplicate and irrelevant rows quickly
09 Removing and Renaming Columns (08:34)
09 Removing and Renaming Columns (08:34)
Learn how to remove unwanted columns and rename the ones you keep
10 Cleaning Text: Capitalization, Removi...
10 Cleaning Text: Capitalization, Removing Spaces and Extracting (15:46)
Fix messy text without writing a single formula
11 Splitting and Merging Columns (10:36)
11 Splitting and Merging Columns (10:36)
Learn how to split one column into two or merge two columns into one
12 Cleaning Data Within the Current Work...
12 Cleaning Data Within the Current Workbook (11:10)
Use Power Query on data already in your workbook
13 Import from SharePoint, OneDrive and ...
13 Import from SharePoint, OneDrive and Teams (11:44)
Connect to cloud-stored files the right way on Mac
14 Creating New Columns: Basic Calculati...
14 Creating New Columns: Basic Calculations (20:12)
Create new columns based on basic arithmetic calculations (add, subtract, divide, multiply)
15 Working with Dates: Date Formats (07:...
15 Working with Dates: Date Formats (07:13)
Fix date format problems caused by regional differences
16 Working with Dates: Calculations (07:...
16 Working with Dates: Calculations (07:54)
Extract days, names, quarters and differences from dates
17 Working with Dates: Calculating Age (...
17 Working with Dates: Calculating Age (05:29)
Calculate current age and years of service automatically
18 Conditional Columns: If-Then Logic (1...
18 Conditional Columns: If-Then Logic (12:10)
Create columns based on conditions
19 Creating Custom Columns: Advanced For...
19 Creating Custom Columns: Advanced Formulas (14:26)
Go beyond the built-in tools by writing your own M code
20 Use Group By to Summarize Data Like a...
20 Use Group By to Summarize Data Like a PivotTable (07:27)
Aggregate your data by category
21 Use Group By to Combine Text (06:35)
21 Use Group By to Combine Text (06:35)
Collate text from multiple rows into a single row
22 Queries: Duplicate v Reference (06:41...
22 Queries: Duplicate v Reference (06:41)
Understand the difference between duplicating and referencing a query
23 Unpivot: Convert a Table to a List (1...
23 Unpivot: Convert a Table to a List (12:23)
Convert columns into rows with Power Query's Unpivot feature
24 Combine Multiple Files Stored in a Si...
24 Combine Multiple Files Stored in a Single Folder (09:17)
Learn how to combine multiple Excel files stored in a single folder
25 Append Queries: Combine Rows from Mul...
25 Append Queries: Combine Rows from Multiple Files (09:46)
Stack rows from multiple tables into one dataset
26 Append Queries: Different Headings (0...
26 Append Queries: Different Headings (08:19)
Handle mismatched column headings with ease
27 Merge Queries: Combine Columns from M...
27 Merge Queries: Combine Columns from Multiple Tables (09:49)
Power Query's equivalent of XOOKUP
28 Merge Queries: Join Types Explained (...
28 Merge Queries: Join Types Explained (04:43)
Choose the right join type for your situation
29 Load Dynamic Arrays into the Query Ed...
29 Load Dynamic Arrays into the Query Editor (09:14)
Use Dynamic Array output as a Power Query source
30 Import a Web Page into Power Query (0...
30 Import a Web Page into Power Query (07:25)
Pull data from the web using Power Query
31 That’s a Wrap! (00:45)
31 That’s a Wrap! (00:45)
A final word and encouragement from Mike
