Excel: Power Query (Get & Transform) (2020)

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.

Topics include:
- What is Power Query (Get & Transform)?
- Working with queries
- Data types explained
- Working with columns and formulas
- Pivoting and unpivoting data
- Grouping data
- Appending queries
- Merging data
- Drill down to use variables
- Fuzzy matching
- Sorting and filtering

Concepts

0. Introduction

01 - Make your data useful with Power Query
02 - What to know before starting this course

1. What Is Power Query (Get & Transform)

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

2. Working with Queries

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

3. Working with Columns

09 - Fill up and fill down
10 - Split column by delimiter
11 - Split into rows
12 - Add conditional and custom columns
13 - Add column by example
14 - Merging columns
15 - Sorting and filtering data in Power Query

4. Working with Formulas

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

5. Pivoting and Unpivoting Data

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

6. Grouping

23 - Group data

7. Appending Queries

24 - Two data sets
25 - Multiple tables
26 - Query data from a folder and import multiple files
27 - Combining table with different headers
28 - Append multiple sheets

8. Merging Data with Joins

29 - Overview of joins in Power Query
30 - Full outer join
31 - Inner join
32 - Left anti join
33 - Right anti join
34 - Left outer join
35 - Right outer join
36 - Outer join versus VLOOKUP
37 - Merge with multiple fields
38 - Joins - Left or right
39 - Approximate match equivalent of VLOOKUP - Binning
40 - Approximate match equivalent of VLOOKUP - Conditional column

9. Drill Down to Use Variables

41 - Drill down to create a variable in Power Query

10. Fuzzy Matching

42 - Matching inconsistent entries by percentage
43 - Merging inconsistent data with a transformation table

11. Custom Data Types

44 - Make and use custom data types

Apply Your Learning with Real-World Challenges

45 - Easy real-world challenge 1 - Pies and prices
46 - Easy real-world challenge 2 - Volunteer hours
47 - Monster real-world challenge - Tracking donations