Microsoft Excel Power Query

DURATION: Half Day

‍

COURSE AIMS

This course aims to provide delegates with a comprehensive introduction to the ‘Get and Transform’ features of Microsoft Excel. Delegates will learn how to import data from a wide range of sources into an Excel workbook, efficiently clean and edit data within the Power Query Editor—such as removing columns, changing data types, and merging tables. By the end of the course, delegates will be able to load queries into Excel to create meaningful charts and reports that support data analysis and decision-making.

‍

COURSE PREREQUISITES

Delegates should have a solid understanding of Microsoft Excel, including proficiency in using formulas and functions, sorting and filtering data, and converting lists into Excel tables.

‍

COURSE OBJECTIVES

On completion of this course, delegates will be able to:

• Understand the Power Query Interface
• Importing Data and Connecting from various sources
• Transform and Combine (merge) the data in Power Query
• Add calculated columns
• Load the data into Excel
• Using the query data to perform reports

‍

COURSE CONTENT

Introduction and Objectives

Introduction to Power Query

• What is Power Query?
• Overview of the Power Query Editor (Interface)
• The four phases (Connect, Transform, Combine and Load)
• Basic concepts
• What are Applied steps?

‍

Connecting to data sources

• Importing data (e.g., CSV/Text, Excel)
• Power Query interface
• Refreshing query

‍

Load and use Data Query

• Load the data into Excel worksheet
• Creating a Connection only
• Create a Pivot Table
• Refreshing the query

‍

Transforming and combining the data

• Combining data tables (Merge and Append)
• Column operations (e.g., Rename, Move, Delete, Duplicate)
• Row operations (e.g., Deleting blank and unnecessary rows)
• Filtering, sorting, splitting, replacing values, changing data types, remove blank rows, changing case
• Remove and replace first row
• Transposing and Unpivoting Data  
• Add a calculated column
• Using the group by (Basic/Advanced)
• Fill up/down feature (show PQE Script)

‍

Hints and Tips

‍

Download PDF Outline