English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 2h 28m | 359 MB
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
Table of Contents
Introduction
1 Make your data useful with Power Query
2 What to know before starting this course
What Is Power Query (Get & Transform)
3 Power Query example
4 Differences between Excel and Power Query
Working with Queries
5 Data types explained
6 Query data from a table or range
7 Query data from another Excel file
8 Load data only as a connection
Working with Columns
9 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
Working with Formulas
16 Use IF formulas
17 Nest IF and AND
18 AddDays to determine deadline
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
Grouping
23 Group data
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
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
Drill Down to Use Variables
41 Drill down to create a variable in Power Query
Fuzzy Matching
42 Matching inconsistent entries by percentage
43 Merging inconsistent data with a transformation table
Apply Your Learning with Real-World Challenges
44 Easy real-world challenge 1 Pies and prices
45 Easy real-world challenge 2 Volunteer hours
46 Monster real-world challenge Who missed the mandatory fun
Resolve the captcha to access the links!