Course Description

This hands-on course introduces you to Microsoft Excel Power Query, a powerful tool for importing, transforming, and preparing data for analysis.

You’ll learn how to connect to multiple data sources, clean and reshape data, and build queries that feed directly into Excel tables or the Data Model.

By the end of the course, you will be confident in using Power Query to streamline data preparation tasks, saving time and improving accuracy in your reporting and analysis.

Target Student

This course is designed for Excel users who regularly work with large or messy datasets and want to simplify the process of cleaning, transforming, and combining data.
It is ideal for:

• Business analysts and data professionals who need to prepare data for reporting
• Financial analysts and accountants working with recurring data imports
• Operations, sales, or marketing professionals who manage data from multiple sources
• Anyone who wants to move beyond manual data cleaning and leverage automation in Excel

Pre-requisites

Attendees should have:

• A good working knowledge of Excel (formulas, tables, and charts)
• Basic familiarity with importing data into Excel
• No prior experience with Power Query is required

Course Requirements

• Microsoft Excel 2016 or later (Power Query is built-in from Excel 2016 onward).
• For earlier versions (Excel 2010/2013), the free Power Query add-in must be installed.
• Access to sample datasets (provided as part of the course materials).

Learning Outcomes

By the end of this course, attendees will be able to:

• Import and connect to multiple external data sources using Power Query
• Clean, transform, and reshape raw data into structured tables
• Combine and manage multiple queries efficiently
• Load data into the Excel Data Model for advanced analysis
• Apply basic advanced query techniques using the M language

Course Content

Lesson 1: Introduction to Microsoft Power Query for Excel

• What is Power Query and why use it?
• Concepts and key components
• Compatible data sources
• Understanding the data import process

Lesson 2: Importing Data from External Sources

• Connecting to Excel workbooks, CSV files, databases, and web data
• Using the default Excel table output
• Loading data into the Data Model

Lesson 3: Transforming Table Data

• Filtering, sorting, and grouping data
• Shaping data for analysis
• Replacing values and applying text, number, date, and time transforms
• Aggregating data
• Unpivoting and transposing tables
• Working with columns: naming, moving, splitting, and merging

Lesson 4: Using the Query Editor

• Understanding query steps
• Editing step settings
• Refreshing queries and managing updates

Lesson 5: Combining Multiple Queries

• Merging queries
• Appending queries
• Managing multiple queries in a workbook

Lesson 6: Shaping Data from Multiple Sources

• Working with table relationships in Power Query
• Navigating linked tables
• Choosing a destination for your data
• Adding queries to Excel worksheets vs. the Data Model
• Advantages of using the Data Model for analysis

Lesson 7: An Introduction to Advanced Queries

• Creating custom queries
• Modifying formulas
• Introduction to the Power Query Formula Language (M)