Microsoft Excel: Introduction to Power Query to Get and Transform Data
Microsoft Excel Training Series | Level 3
Duration: 1 day (9 am – 4 pm)
Microsoft® Excel® Versions: 2016 | 2019 | 365 (Windows)
Delivery Methods (Instructor-led): Live classroom (LC) | Virtual classroom (VC)
Course Dates: April 23, 2021 (Virtual classroom) | View schedule
Course Fee: $275 CDN per person + HST (Virtual classroom), $295 CDN per person + HST (Bring your own device for live classroom courses) or $345 CDN per person + HST (Avantix Learning provides device for live classroom courses)*
Custom training: This course may be delivered at your site or ours as an instructor-led or virtual classroom solution. Contact us at firstname.lastname@example.org for more information including savings for custom group training.
Public scheduled courses are delivered as live classroom training or virtual classroom training (as indicated). All courses are instructor-led.
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
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 from multiple sheets into one sheet or data source
- Combining data from multiple sources
- 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
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 email@example.com if you'd like to arrange custom training on a date that's convenient for you.
Copyright 2021 Avantix® Learning
You may like
You can quickly fill blank cells in a range of data in Excel with dashes, zeros or other number or text values. Blank cells can be problematic if you want to use a data set as the source for a pivot table.
You can convert cm to inches (or inches to cm) using formulas with operators or functions in Excel. This is useful since the regional settings on a computer affect the default measurement system used in Excel and other programs.
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 can delete blank rows in Microsoft Excel worksheets manually or you can use several tricks to remove multiple blank rows quickly. Check out 5 different ways to easily delete blank rows in your data.
Moving a pivot table is not as simple as moving other objects in an Excel worksheet or workbook. You will typically need to use the Move PivotTable command in the Ribbon to move a pivot table to a different area on a worksheet or to a different sheet in the same workbook.
You may also like
During a PowerPoint slide show, you can change your mouse into a laser pointer to focus attention on a specific area on your slide. You can show or hide the built-in laser pointer using keyboard shortcuts or by using the context menu.
You can add audio in PowerPoint from a file on your PC or from a shared drive. Audio is played during a slide show and you can insert music, sound clips, or voice recordings. Since audio files are copied into PowerPoint presentations by default, they can increase file size and may need to be compressed. To play audio, your computer must be equipped with a sound card and speakers.
Check out these three easy ways to copy shape, picture or text formatting quickly and easily in PowerPoint using buttons or keyboard shortcuts.
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 | firstname.lastname@example.org