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 | 365 (Windows)

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

Course Dates: October 22, 2021 (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.

Live classroom courses: Our instructor-led live classroom (in-person) courses are held in downtown Toronto at the Toronto Star Building, 1 Yonge Street, Suite 1801. 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 or virtual classroom 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 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

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some live 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 training on a date that's convenient for you.

Copyright 2021 Avantix® Learning

Microsoft Excel training man pointing to icons.

You may like

3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells

3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells

You can apply strikethrough to text or values in Excel to cross out or create a line through a cell or part of a cell. There are 3 common ways to apply strikethrough in your Excel worksheets – using a built-in keyboard shortcut, using the Font dialog box, or by adding a command to the Quick Access Toolbar.

How to Lock the Position of a Slicer in Excel

How to Lock the Position of a Slicer in Excel

Slicers are a great tool in Excel to filter tables or pivot tables. You can lock the position of a slicer in an Excel worksheet using a two-step process. First, select the slicer or slicers and Disable resizing and moving in the Format Slicer task pane (and choose other options based on your preferences). Second, protect the sheet using the Review tab in the Ribbon. There are a few important options you’ll need to select to get this to work.

You may also like

3 Ways to Change the Font on All Slides in PowerPoint

3 Ways to Change the Font on All Slides in PowerPoint

You can insert or type the degree symbol in PowerPoint presentations using built-in commands or keyboard shortcuts. The degree sign can be entered in placeholders, text boxes or in equation blocks on PowerPoint slides.

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-person in Toronto)
Location
Avantix Learning, 1 Yonge Street, Suite 1801 (Toronto Star Building),Toronto, Ontario, Canada,-M5C 1S8
Next course starts
October 22, 2021
Next course ends
October 22, 2021

Pin It on Pinterest