Data Preparation with Power Query

Instructor: Alaa Hady · Language: English or Arabic · Suggested duration: 1 day · Level: Beginner to Intermediate (Level 200)

Objectives

• Standardize messy, inconsistent business data into analysis-ready tables.
• Build repeatable, self-documenting data transformation pipelines.
• Combine data dynamically from multiple folders, sheets, and disparate sources.
• Maximize refresh speed using Query Folding and troubleshoot common M errors.

Outline and contents

1. Power Query Editor architecture, M language fundamentals, and managing Applied Steps
2. Handling data types, regional locales, nulls, errors, and duplicate keys
3. Text, number, and date transformations: splitting, extracting, conditional columns, and Column from Examples
4. Combining queries: Merge (join types and fuzzy matching) versus Append
5. Automating folder ingestion and handling schema drift across monthly files
6. Reshaping data: Pivoting, Unpivoting, Grouping, and transposing complex spreadsheet headers
7. Parameters, custom functions, and staging queries for modular architecture
8. Query Folding mechanics, viewing native queries, and performance-aware data preparation
9. Data quality profiling (column quality, distribution, profile) and refresh troubleshooting
10. Intensive hands-on data-wrangling lab

Target audience

Power BI developers, Excel power users, data analysts, finance analysts, and reporting teams.

Expected outcomes

Participants can automate complex data cleaning routines, ingest multi-file structures reliably, preserve query folding for fast refreshes, and resolve broken transformation pipelines.