Power Query: Data Transformation Mastery

Course Length: 1 Day 

Audience: Business analysts, Excel users, Power BI beginners

Prerequisites: Basic knowledge of Excel or Power BI

Module 1: Introduction to Power Query 

• What is Power Query?
• Power Query vs. Excel formulas vs. VBA
• Where Power Query lives (Excel vs. Power BI)
• The Power Query interface (Navigator, Formula Bar, Query Settings)

Activity: Load a simple CSV file into Power Query and explore the interface.

Module 2: Connecting to Data Sources 

• Importing from Excel, CSV, Web, and databases
• Combine multiple files from a folder
• Refreshing and managing connections

Activity: Connect to multiple CSVs in a folder and append them.

Module 3: Transforming Data 

• Removing rows/columns
• Filtering and sorting
• Splitting and merging columns
• Changing data types
• Fill down/up and transpose

Activity: Clean up a messy sales report using these tools.

Module 4: Shaping Data for Analysis 

• Unpivot and pivot columns
• Grouping and summarizing
• Creating calculated columns
• Using the “Applied Steps” panel efficiently

Activity: Reshape survey data from wide to long format and summarize responses.

Module 5: Combining Queries 

• Append vs. Merge explained
• Merge join types (Inner, Left Outer, etc.)
• Creating relationships for modeling

Activity: Merge customer and order tables to build a single clean dataset.

Module 6: Intro to the M Language 

• Viewing and modifying the formula bar
• Common M functions: Text, Table, List, Record
• Custom columns and basic logic

Activity: Write a custom column using M to classify sales as “High,” “Medium,” or “Low”.

Module 7: Loading and Using Queries (30 mins)

• Load to Excel sheet vs. Power Pivot
• Load to Power BI Model
• Refresh strategies and query dependencies

Activity: Load cleaned data into Excel pivot table or Power BI table visual.

Wrap-Up & Q&A (30 mins)

• Best practices for Power Query projects
• Where to go next: M Language deep dive, performance optimization, Power BI integration

This event ticket will close at 12:00 pm the day before the event.