Excel: Power Query (Get & Transform)

Course details

Power Query is a feature in Excel that allows you to quickly import data from multiple sources and easily clean, transform, and reshape it to suit your needs. Follow along with Excel MVP Oz du Soleil as he shows you how to use this powerful, time-saving tool. Oz demonstrates how to import, merge, rearrange, and clean data, as well as how to repeat the process with one click if the data changes. Discover how to split columns, unpivot data, and use joins to merge, segment, and compare datasets. Oz offers dozens of techniques and tips in this comprehensive training.

Concepts

0. Introduction

01 - Make your data useful with Power Query

1. What Is Power Query (Get & Transform)

02 - Power Query example
03 - Differences between Excel and Power Query

2. Working with Queries

04 - Data types explained
05 - Query data from a table or range
06 - Query data from another Excel file
07 - Load data only as a connection

3. Working with Columns

08 - Fill up and fill down
09 - Split column by delimiter
10 - Split into rows
11 - Add conditional and custom columns
12 - Add column by example
13 - Merge columns
14 - Sort and filter data in Power Query

4. Working with Formulas

15 - Use IF formulas
16 - Nest IF and AND
17 - AddDays to determine the deadline

5. Pivoting and Unpivoting Data

18 - Pivot data in Power Query
19 - Pivot and append data
20 - Pivot and don't aggregate
21 - Unpivot data in Power Query
22 - Unpivot warnings

6. Grouping

23 - Group By feature

7. Appending Queries

24 - Two data sets
25 - Multiple tables
26 - Query data from a folder and import multiple files
27 - Append multiple sheets

8. Midterm Challenge

28 - Midterm challenge - Get a count of all colors

9. Merging Data with Joins

29 - Overview of joins in Power Query
30 - Walk through all six joins
31 - Joins - Left or right
32 - Outer join versus XLOOKUP
33 - Merge with multiple fields
34 - Approximate match equivalent of VLOOKUP - Binning
35 - Approximate match equivalent of VLOOKUP - Conditional column
36 - Cross Join

10. Drill Down to Create Variables

37 - Drill down to create a variable in Power Query

11. Fuzzy Matching

38 - Fuzzy matching by percentage
39 - Merging inconsistent data with a transformation table

12. Apply Your Learning with Real-World Challenges

40 - Challenge setup
41 - Real-world challenge 1 - Projects
42 - Real-world challenge 2 - Donors
43 - Real-world challenge 3 - Soccer team

Conclusion

44 - Next steps