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: February 7, 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.

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 Power Query 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 tables into one sheet or data source
  • Combining data from multiple files

Creating Calculations

  • Creating calculations without using formulas
  • 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 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 onsite training or an online course on a date that's convenient for you.

Copyright 2022 Avantix® Learning

Microsoft Excel training man pointing to icons.

You may like

Use Excel Conditional Formatting to Highlight Dates Before Today (3 Ways)

Use Excel Conditional Formatting to Highlight Dates Before Today (3 Ways)

You can use conditional formatting in Excel to highlight cells containing dates before today or within a date range before the current date. In a worksheet, you can use conditional formatting to highlight selected cells by filling them with a color based on rules or conditions. This type of formatting is helpful if you want to highlight past due dates such as invoices that are 30, 60 or 90 days overdue.

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.

You may also like

How to Reorder Objects in PowerPoint Using Bring to Front or Send to Back (with Shortcuts)

How to Reorder Objects in PowerPoint Using Bring to Front or Send to Back (with Shortcuts)

You can reorder objects on a PowerPoint slide using keyboard shortcuts, the context menu, the Ribbon or the Selection Pane. When objects are placed on a slide, they are stacked on top of each other. It’s common to reorder objects to change the stacking order. When you select an object and Send to Back, it’s moved to the bottom of the stack. If you choose to Send Backward, it’s moved back by object. When you select an object and Bring to Front, it’s moved to the top of the stack. If you choose to Bring Forward, it’s moved forward by object.

Use Excel Conditional Formatting to Highlight Dates Before Today (3 Ways)

Use Excel Conditional Formatting to Highlight Dates Before Today (3 Ways)

You can use conditional formatting in Excel to highlight cells containing dates before today or within a date range before the current date. In a worksheet, you can use conditional formatting to highlight selected cells by filling them with a color based on rules or conditions. This type of formatting is helpful if you want to highlight past due dates such as invoices that are 30, 60 or 90 days overdue.

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
February 7, 2022
Next course ends
February 7, 2022

Pin It on Pinterest