Power Query Advanced
For data that comes from five places and agrees with none of them.
Advanced2 days6 modulesHands-onHRD Corp claimable
Real data rarely arrives clean or in one piece. Customer names are spelt three ways, the ERP export doesn’t match the CRM, and the same query has been copied into six workbooks.
This course goes past the basics: calculated, conditional and custom columns; appending and merging across sources; inner, outer and anti joins; fuzzy matching with similarity thresholds; parameterised, reusable queries; and grouping, pivoting and unpivoting into model-ready tables. It ends with an end-to-end build that uses the practices that keep queries fast and easy to maintain.
By the end, participants can
- Apply advanced Power Query techniques to clean, transform and structure data using calculated, conditional and custom columns
- Combine data from multiple sources using appending, merging and appropriate join strategies
- Improve data quality using filtering, error handling, anti joins and fuzzy matching
- Build dynamic, reusable and scalable queries using parameters and query references
- Prepare analysis-ready datasets through reshaping, grouping and summarising data while applying performance and maintainability best practices
Course outline
Day 1
01Advanced Data Transformation & Custom Logic
- Create calculated, conditional and custom columns
- Apply rule-based transformation logic
- Split columns and remove duplicates
- Apply best practices for readable and scalable transformations
02Multi-Source Data Integration & Query Management
- Append data from multiple sources
- Understand query duplication versus referencing
- Build reusable and modular queries
- Manage query dependencies for scalable data workflows
03Relational Data Modelling & Merge Queries
- Understand relational joins in Power Query
- Apply inner, left, right and full outer joins
- Select appropriate join strategies for different analytical requirements
- Use anti joins to identify missing or unmatched records
Day 2
04Data Quality & Advanced Matching
- Apply basic and advanced filtering techniques
- Identify and manage data errors
- Perform fuzzy matching and fuzzy merges
- Configure similarity thresholds and transformation tables to improve match accuracy
05Dynamic Queries, Grouping & Data Reshaping
- Create and manage query parameters
- Build flexible and reusable parameterised queries
- Group and summarise data using multiple aggregation methods
- Pivot and unpivot data for analytical requirements
06End-to-End Power Query & Best Practices
- Design complete Power Query workflows in Excel
- Combine transformation, integration and modelling techniques
- Optimise query performance and maintainability
- Troubleshoot common issues and design pitfalls
- Prepare transformed data for reporting and downstream modelling