Data transformation
Mastering Power Query
Automate data cleaning, combine recurring files, reshape datasets, and create repeatable transformation workflows in Excel and Power BI.
Replace repetitive cleanup with refreshable data workflows.
Designed for analysts and report owners who regularly import, clean, combine, and reshape data from multiple sources.
Connect to common files, folders, tables, and workbooks.
Clean and standardize data with repeatable steps.
Merge, append, pivot, and unpivot datasets.
Build refreshable outputs for Excel and Power BI.
Course overview
A practical, instructor-led program with guided exercises and workplace examples. Open a topic to see the coverage.
1. Introduction to Power Query
- What is Power Query?
- Where to find it in Excel and Power BI
2. Connecting to Data Sources
- Importing from Excel files, CSV, TXT
- Connecting to external sources (databases, web data, SharePoint, folders)
3. Fundamentals of Data Shaping
- Removing rows/columns
- Filtering and sorting data
4. Combining Queries
- Append Queries (stacking tables)
- Merge Queries (VLOOKUP equivalent)
5. Data Cleaning Tools
- Remove duplicates
- Trim and clean text
6. Transforming Data
- Pivoting and unpivoting columns
- Group By (aggregating data)
Full course outline
Review the complete legacy course outline, including all detailed subtopics.
View full outline
- 1. Introduction to Power Query
- What is Power Query?
- Where to find it in Excel and Power BI
- Benefits vs. traditional Excel tools (manual cleaning, formulas, VBA)
- Overview of the Power Query interface (Ribbon, Queries pane, Applied Steps)
- 2. Connecting to Data Sources
- Importing from Excel files, CSV, TXT
- Connecting to external sources (databases, web data, SharePoint, folders)
- Differences between "Load to Table" and "Load to Data Model"
- 3. Fundamentals of Data Shaping
- Removing rows/columns
- Filtering and sorting data
- Splitting and merging columns
- Changing data types
- Using "Applied Steps" and undoing transformations
- 4. Combining Queries
- Append Queries (stacking tables)
- Merge Queries (VLOOKUP equivalent)
- Understanding join types: Left, Right, Inner, Full Outer
- 5. Data Cleaning Tools
- Remove duplicates
- Trim and clean text
- Fill down / fill up values
- Replace values (manual and advanced)
- Handling nulls and blanks
- 6. Transforming Data
- Pivoting and unpivoting columns
- Group By (aggregating data)
- Conditional columns (IF logic inside Power Query)
- Extracting text (LEFT, RIGHT, MID equivalents)
- Working with date/time transformations