Zomalex | Data and AI Training

Home | Power BI | Excel | Python | SQL | Gen AI | Contact Us | LBAG | info@zomalex.co.uk

Excel Power Query Course Outline

Who should attend

This course is for anybody who needs to clean and prepare data before using it in Excel. Excel’s Power Query can import and clean data so that it is ready in Excel’s sheets for analysis and reporting. This will help attendees avoid hours of manual repetitive work. Attendees will also gain highly sought-after skills in Excel and Power Query.

Learning Objectives

Attendees will be able to use Power Query to automate the preparation of their data: for example, import data from several different sources, remove duplicate rows, combine datasets - then load the cleaned data into their Excel sheets.

Course Content

The course has three modules:

• getting started with Power Query
• data preparation with Power Query
• using generative AI assistants to help with Power Query challenges

Getting Started with Power Query

This module covers how to:

• launch Power Query from within Excel
• become familiar with the Power Query user interface
• connect to data sources in various formats, such as text files, Excel files and database tables

Data Preparation with Power Query

This module covers several data preparation techniques, for example how to:

• import data from any source: another Excel sheet, text file, database
• clean the data, e.g., removing headers and footers that are not part of the data,
• reshape the data, e.g., pivot or unpivot, merge or stack data,
• find and fix bad data such as missing values and outliers,
• and automate the above steps so that they can be repeated on demand.
• remove rows or columns, split columns
• add new columns
• merge or append datasets

Use Generative AI Assistants to help with Power Query challenges

This module covers how to use generative AI assistants such as Copilot, Claude or ChatGPT to help us with Power Query challenges. For example, we can ask these assistants to write M code for us to perform a specific data transformation, or to explain how to use a particular Power Query feature. This can save time and help us learn Power Query more quickly.

Course Length

1 day

Pre-requisites

Attendees will need Excel 365 installed on their PC.

Snapshots from the course exercises

Excel’s Power Query user interface