Microsoft Excel: Introduction to Power Query to Get and Transform Data

Microsoft Excel Training Series | Level 3

Course Details

Duration: 1 day (9 am – 4 pm)

Microsoft® Excel® Versions: 2016 | 2019 | 2021 | 365 (Windows)

Delivery Methods (Instructor-led): In-person (Live classroom) | Virtual classroom

Course Dates: October 17, 2022 (Virtual classroom) | View schedule Course schedule

Course Fee: $275 CAD per person + HST (Virtual classroom), $295 CAD per person + HST (Bring your own device for in-person courses) or $345 CAD per person + HST (Avantix Learning provides device for in-person courses)*

Timing: Public scheduled courses run from 9:00 am to 4:00 pm (Eastern Time).

Virtual classroom courses: Our instructor-led virtual classroom courses are delivered in a virtual classroom environment. Students will be sent a virtual classroom invitation prior to the course.

In-person classroom courses: Our instructor-led, live classroom (in-person) courses are held in downtown Toronto at 18 King Street East, Suite 1400, Toronto, Ontario, Canada. Some courses are also held at an alternate downtown Toronto location.

Custom training: This course may be delivered at your site or ours as an instructor-led virtual classroom or in-person solution. Contact us at info@avantixlearning.ca for more information including savings for custom group training.

Register for this training course.

Course Overview

In this course, students will learn to import, connect to, and manipulate data using Excel's amazing Power Query tool (also called Get and Transform). Power Query is part of Excel's suite of Business Intelligence (BI) tools. Students will create, name, edit and refresh queries and connect to both Excel workbooks and external data sources. In the Query Editor, columns will be added, removed, split and merged. Using Power Query, participants will learn to clean different types of data including text, numbers and dates. Data will be pivoted and unpivoted and will be combined in different ways. Students will also learn to create calculations using Power Query's "M" language and will create a pivot table from queried data.

Prerequisite: Microsoft Excel: Intermediate / Advanced or equivalent knowledge and skills.

Related training: View all Microsoft Excel courses >

INCLUDED IN THIS COURSE

  • Comprehensive course manual or published book
  • Keyboard shortcuts quick reference
  • Sample and exercise files
  • Refreshments (for classes conducted in Avantix Learning classrooms)
  • Certificate of completion
  • Follow-up email support

Course topics

Getting Started with Power Query

  • Overview of the capabilities of Power Query
  • Using different data sources

Creating a Query

  • Using headings in data
  • Creating a query by importing or loading data from a workbook
  • Using Excel tables as the source of a query

Working in the Power Query Editor

  • Working in the Power Query Editor
  • Understanding the different tabs in the Power Query Ribbon
  • Setting columns to the correct data type
  • Filtering columns
  • Right-clicking column headings to access different commands
  • Performing basic transformations and adding steps to a query
  • Navigating through Applied Steps
  • Removing steps from a query

Loading or Connecting to a Query

  • Loading queried data into Excel
  • Creating a connection only query

Viewing, Refreshing and Editing a Query in Excel

  • Viewing a query in Excel
  • Refreshing a query
  • Editing a query

Working with Columns in Power Query

  • Inserting columns
  • Removing columns
  • Combining columns
  • Splitting columns

Cleaning and Transforming Data

  • Removing extra spaces
  • Changing case
  • Removing blank rows
  • Removing rows with errors
  • Filling empty cells with data
  • Removing duplicates
  • Extracting the left, right or middle characters in a column
  • Transforming data using pivot or unpivot
  • Working with dates

Combining Data

  • Combining data from multiple tables into one table using an Append query
  • Combining data from tables using a Merge query

Creating Calculations

  • Creating calculations in different ways
  • Creating calculations using Power Query's M language
  • Understanding basic syntax
  • Creating conditional calculations

Combining Data from Multiple Sources

  • Importing data from different sources
  • Connecting to data from different sources
  • Combining files from a folder (such as mutiple workbooks or csv files)

Creating a Pivot Table from a Query

  • Creating a pivot table from a query
  • Refreshing the query and the pivot table
Register Now for a public course at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada.

Do you need more information? Contact us!

To request this page in an alternate format, contact our staff.

*Prices subject to change

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2022 Avantix® Learning

Microsoft Excel training man pointing to icons.

You may like

How to Password Protect Your Excel Worksheets and Workbooks

How to Password Protect Your Excel Worksheets and Workbooks

You can password protect worksheets and workbooks in Excel. If you want to prevent other users from opening or modifying an Excel workbook, you can encrypt the file with a password. You can also protect workbook structure where you can prevent users from deleting, renaming, moving or unhiding worksheets. Password protection can be added to your Excel file in several ways.

How to Use Cell Styles in Excel to Save Time Formatting

How to Use Cell Styles in Excel to Save Time Formatting

You can apply cell styles in Excel worksheets to quickly format cells in a consistent way and automate repetitive formatting. Excel includes several built-in styles (such as Heading 1, Heading 2 and so on) and you can modify styles before or after you apply them. If none of the built-in styles fit your needs, you can also create a new style.

You may also like

How to Password Protect Your Excel Worksheets and Workbooks

How to Password Protect Your Excel Worksheets and Workbooks

You can password protect worksheets and workbooks in Excel. If you want to prevent other users from opening or modifying an Excel workbook, you can encrypt the file with a password. You can also protect workbook structure where you can prevent users from deleting, renaming, moving or unhiding worksheets. Password protection can be added to your Excel file in several ways.

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca

Upcoming Training
Course
Microsoft Excel: Introduction to Power Query to Get and Transform Data (Virtual classroom course or in-person in Toronto)
Location
Avantix Learning, 18 King Street East, Suite 1400,Toronto, Ontario, Canada,
Next course starts
October 17, 2022
Next course ends
October 17, 2022

Pin It on Pinterest