Excel: Power Query (Get & Transform)
3h 43mIntermediate2024-04-18
Authors
Oz du Soleil
Excel MVP, Author, and Trainer
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.
Skills covered
SpreadsheetsMicrosoft ExcelData AnalysisData ScienceBusiness Analysis and StrategyBusiness Software and ToolsMicrosoftDeep Dive (X:Y)
Concepts
Introduction
- Make your data useful with Power Query
What Is Power Query (Get & Transform)
- Power Query example
- Differences between Excel and Power Query
Working with Queries
- Data types explained
- Query data from a table or range
- Query data from another Excel file
- Load data only as a connection
Working with Columns
- Fill up and fill down
- Split column by delimiter
- Split into rows
- Add conditional and custom columns
- Add column by example
- Merge columns
- Sort and filter data in Power Query
Working with Formulas
- Use IF formulas
- Nest IF and AND
- AddDays to determine the deadline
Pivoting and Unpivoting Data
- Pivot data in Power Query
- Pivot and append data
- Pivot and don't aggregate
- Unpivot data in Power Query
- Unpivot warnings
Grouping
- Group By feature
Appending Queries
- Two data sets
- Multiple tables
- Query data from a folder and import multiple files
- Append multiple sheets
Midterm Challenge
- Midterm challenge - Get a count of all colors
Merging Data with Joins
- Overview of joins in Power Query
- Walk through all six joins
- Joins - Left or right
- Outer join versus XLOOKUP
- Merge with multiple fields
- Approximate match equivalent of VLOOKUP - Binning
- Approximate match equivalent of VLOOKUP - Conditional column
- Cross Join
Drill Down to Create Variables
- Drill down to create a variable in Power Query
Fuzzy Matching
- Fuzzy matching by percentage
- Merging inconsistent data with a transformation table
Apply Your Learning with Real-World Challenges
- Challenge setup
- Real-world challenge 1 - Projects
- Real-world challenge 2 - Donors
- Real-world challenge 3 - Soccer team
Conclusion
- Next steps