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

Microsoft Excel Training Series | Level 4

Course Details

Duration: 1 day (9 am – 4 pm)

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

Delivery Methods: Instructor-led classroom (ILC) | Virtual classroom (ILVC)

Course Dates: November 10, 2020 (Virtual classroom) | December 30, 2020 (Virtual classroom) | View schedule Course schedule

Course Fee: $245 CDN per person + HST (virtual classroom), $275 CDN per person + HST (bring your own device) or $325 CDN per person + HST (Avantix Learning provides device)*

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

Note: Public scheduled courses are delivered as live instructor-led classroom training or virtual classroom training (as indicated). Bring your own device rates apply to live classroom courses.

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.

Location and timing: Public scheduled courses are held online in virtual classroom format or in downtown Toronto and run from 9:00 am to 4:00 pm (Eastern Time).

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 Power Query with other tools

Creating a Query

  • Creating a query
  • Naming a query
  • Adding steps to a query
  • Removing steps from a query
  • Loading vs connecting
  • Refreshing a query

Working in the Query Editor

  • Overview of the Query Editor
  • Using the different tabs in the Ribbon
  • Right-clicking column headings to access different commands
  • Filtering columns
  • Sorting columns

Connecting to Different Data Sources

  • Common source types
  • Importing data from different sources
  • Connecting to data from different sources
  • Connecting to folders

Working with Columns in Power Query

  • Converting columns to different types of data
  • Inserting columns
  • Removing columns
  • Combining columns
  • Splitting columns

Cleaning and Transforming Data

  • Formatting text, number and date columns
  • Removing spaces
  • Changing case
  • Removing blank rows
  • Removing rows with errors
  • Filling in data in empty cells
  • 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 sheets into one sheet or data source
  • Combining data from multiple sources

Creating Calculations

  • Creating calculations using Power Query’s “M” language
  • Understanding basic syntax
  • Creating common calculations

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

Copyright 2020 Avantix® Learning 

Instructor writing on virtual graph in Microsoft Excel Power Pivot training course in Toronto.

You may like

How to Hide Comments or Notes in Excel Workbooks

How to Hide Comments or Notes in Excel Workbooks

You can hide comments and notes in Excel workbooks. In addition to adding comments in Excel, there is a little known function called the N function that you can use to enter notes directly within a formula. The N function has been around for a long time so you can use it in older versions of Excel.

You may also like

How to Hide Comments or Notes in Excel Workbooks

How to Hide Comments or Notes in Excel Workbooks

You can hide comments and notes in Excel workbooks. In addition to adding comments in Excel, there is a little known function called the N function that you can use to enter notes directly within a formula. The N function has been around for a long time so you can use it in older versions of Excel.

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 | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Upcoming Training
Course
Microsoft Excel: Introduction to Power Query to Get and Transform Data (Virtual classroom course or in Toronto)
Location
Avantix Learning, 1 Yonge Street, Suite 1801 (Toronto Star Building),Toronto, Ontario, Canada,-M5C 1S8
Next course starts
November 10, 2020
Next course ends
November 10, 2020

Pin It on Pinterest