Great Canadian Training Company color logo

Microsoft Excel Power Query

Microsoft Excel Power Query

Course Length:

6 hours (1 day)

Learning Objectives

In this course, you will become familiar with Power Query and its advanced selection of features. You will learn how to connect to external data sources, transform and cleanse your data, pivot and transpose your data, and create custom calculated columns.

 

  • Connect to data sources
  • Transform and cleanse data
  • Send Power Query data to other destinations
  • Pivot and transpose data
  • Group Power Query data
  • Create calculated columns
  • Understand Power Query parameters

Target Student

This course is for individuals who are familiar with the basics of Excel and who wish to further their learning about Power Query.

Course Outline

Section 1: Getting Started

Introduction to Power Apps

Versions and Compatibility

The Power Excel Workflow

When to Use Power Query and Power Pivot

What Is Power Query?

Power Query Interface

 

Section 2: Connecting to Data Sources

Introduction to Connecting to Data Sources

Loading Data from an Excel File and Excel Table

Loading Data from a Text File

Loading Data from a Database

Loading Data from a Folder

Loading Data from the Web

 

Section 3: Transforming and Cleansing Data

Introduction to Cleansing Data

Editing Queries

Editing Column Headers

Understanding Data Types

Splitting Column Data

Replacing Data

Sorting and Filtering Data

Removing Duplicates from a List

Using the Fill Command

 

Section 4: Sending Power Query Data to Other Destinations

Loading to an Excel Workbook

Loading to a Data Model

Power Query Load Settings

Power Query Refresh Settings

Deleting Power Queries

 

Section 5: Pivot and Transpose Data

Transposing Query Data

Pivoting and Unpivoting Column Commands in Action

 

Section 6: Grouping Power Query Data

Duplicating a Query

Grouping and Summarizing Data

Advanced Data Grouping

 

Section 7: Creating Custom Calculated Columns

Creating Conditional Columns

Creating a Date Calculation

Creating a Custom Column Calculation

Creating an Index Column


Section 8: Working with Multiple Data Sources

Working with Multiple Excel Tables

Understanding Table Relationships

Merging Queries

Appending Queries

 

Section 9: Advanced Transformations

Understanding the Importance of Clean, Organized Data

Converting Columns to Rows

Cleaning Up Nested Data

 

Section 10: Power Query Parameters

Understanding Power Query Data Parameters

Working with Parameters

Managing Parameters

Power Query List Parameters

A seal with “Guaranteed to Run” written on it. In the middle is a person giving a thumbs up, standing behind a checkmark

Microsoft Excel Power Query

Upcoming Classes

All Public classes are held online
with a live instructor
Dates Price Qty
March 5, 2024
9:00 AM to 4:00 PM ET
$495.00 Online
June 6, 2024
9:00 AM to 4:00 PM ET
$495.00 Online
October 22, 2024
9:00 AM to 4:00 PM ET
$495.00 Online
March 11, 2025
9:00 AM to 4:00 PM ET
$495.00 Online
* Applicable taxes will be applied on checkout

I want to talk to someone about private or customized training


Have questions about this course? See below or reach out to our training coordinators.

Group of 4 or more? Consider private training

The Great Canadian Training & Consulting Company Advantage

When you train with us, your training will not just check a box. We make sure it is GREAT!

  • Experienced Trainers
  • Guaranteed to run classes
  • Learning Guarantee
  • Anytime after-training support for 30 days
  • Bilingual training, manuals & post-training support available

This course comes with

PDF Reference Guide

Take the Reference Guide anywhere and save the environment with an electronic copy of your training Reference Guide for use on any of your computers or devices


Use Your Own Files

Whether it's one-on-one or group training you can give us files to include in your training session and help you apply what you learn even more!


Printed Reference Guide

Instead of the PDF that comes with your course, purchase a printed paper Reference Guide for use during your class and future reference