Excel Power Query: Clean & Transform Data

This course introduces you to Excel's powerful data transformation tool - Power Query - and teaches you how to automate repetitive tasks, clean messy data, and prepare it for reliable analysis.

You'll discover how to connect to multiple data sources, merge and reshape information, and apply powerful transformation techniques - all without the need for complex formulas. Save time, reduce errors, and take control of your data. 

Course Objectives

By the end of this course, you’ll be able to:

• Confidently connect, combine, and shape data from multiple sources

• Clean and prepare messy data using practical Power Query techniques

• Automate manual processes that previously took hours

• Use advanced features like unpivoting, merging, and conditional columns with ease

• Build a reliable, repeatable data preparation process for better analysis and reporting

Who Is This Course For?

This course is ideal for:

• Professionals who regularly work with large or messy datasets and want a faster, more reliable way to prepare them for analysis.

• Excel users who currently spend hours on repetitive data-cleaning tasks and want to automate the process.

• Analysts, managers, and business users who need to combine information from multiple sources into accurate, usable reports.

Prerequisites: A good working knowledge of Excel is required. Prior experience with PivotTables and formulas will be helpful, but no coding knowledge is required.

Course Outline

Understanding Power Query

• Introduction to Power Query and what it can do

• Overview of interface and key features

• Typical data problems Power Query can solve

• How you can apply Power Query to your data

Getting Started with Power Query

• Power Query interface walkthrough

• Understanding data source connections and import best practices

Data Preparation: Part 1 – Combining and Connecting Data

• Importing data from folders and the web

• Merging queries vs. appending queries

• Understanding JOIN types and using them effectively

• Performing fuzzy lookups for flexible matching

• Merging queries with multiple key columns

Data Preparation: Part 2 – Cleaning and Shaping

• Using first rows as headers

• Changing case, trimming spaces, and cleaning data

• Working with data types and formats

• Removing duplicates and blank rows

• Splitting and merging columns

• Adding conditional columns for custom logic

• Fixing date issues using locale settings

Data Preparation: Part 3 – Restructuring for Analysis

• Unpivoting columns for flexible analysis

• Using Group By to summarise data

• Transposing data for alternate views

Reflections & Planning

• Recap of key Power Query techniques

• Discuss how automation can transform your reporting

• Identify next steps to integrate Power Query into your workflow