Copyright 2019 Avantix® Learning Inc.

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

Microsoft Excel Training Series | Level 4

Course Details

Duration: 1 day

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

Category: Microsoft Excel Power BI (Business Intelligence) Tools

Delivery Methods: Instructor-led classroom (ILC) | Virtual classroom (VC) on request

Course Dates: May 27, 2019 | View schedule Course schedule

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

Note: Public scheduled dates for this course are delivered as live instructor-led classroom 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.

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.

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

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

You may like

How to Create 3D References in Microsoft Excel

You can use 3D references in Excel formulas to calculate across multiple worksheets that are structured in the same way. A 3D reference refers to the same cell or range of cells on multiple worksheets.

How to Freeze Row and Column Headings in Microsoft Excel

As many Excel worksheets can become quite large, it can be useful to freeze row and column headings so that they are locked in place when you scroll through your worksheet. In Excel, you can freeze both row headings and column headings or just one.

You may also like

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 (Microsoft Excel Power BI Training)
Location
Avantix Learning Inc., 1 Yonge Street, Suite 1801 (Toronto Star Building),Toronto, Ontario, Canada,-M5C 1S8
Next course starts
May 27, 2019
Next course ends
May 27, 2019

Pin It on Pinterest