Skills learned in the Power Query Course
- Data cleaning and transformation with Power Query
- Importing data from files, folders, web pages, and cloud storage
- Reshaping report-style data into analysis-ready tables
- Combining data with merge and append queries
- Automating data preparation with refreshable queries
- Query troubleshooting and data quality profiling
- M language fundamentals
- AI-assisted data analysis with Copilot in Excel
- Using Power Query in both Excel and Power BI
Power Query Course - Course Dates
All classes are led by a live instructor. Class times listed are Eastern time.
Power Query course topics covered
Introduction to Power Query in Excel and Power BI
Understand what Power Query is, why it is the same engine in Excel and Power BI, and how it replaces manual, repetitive data preparation.
- What Power Query is and why it matters
- The data analysis process: extract, transform, load, and refresh
- One engine in two applications: what is the same and what is different
- Opening the Power Query Editor in Excel (Get & Transform) and in Power BI Desktop (Transform data)
- Navigating the Power Query Editor
- Power Query options and settings
- How Applied Steps record and replay your work
- Structuring source data for Power Query
- Refreshing queries
Connecting to Data Sources
Bring data in from the files and systems you already use, and keep the connection live for future refreshes.
- Importing from Excel workbooks and worksheets
- Importing CSV and text files
- Importing from a folder of files
- Importing tables from web pages
- Importing tables from PDF files
- Connecting to files stored in SharePoint and OneDrive
- Editing data source settings and changing file locations
- Connector differences between Excel and Power BI
Cleaning and Transforming Data
Fix the common problems in raw data so it is accurate and consistent before analysis.
- Setting and correcting data types
- Removing, renaming, and reordering columns
- Removing blank rows, errors, and duplicates
- Removing top and bottom rows
- Filtering and sorting rows
- Trimming, cleaning, and changing the case of text
- Splitting and merging columns
- Splitting columns into rows
- Replacing values and filling down blank cells
- Extracting text, dates, and parts of dates
- Math calculations on columns
- Date and duration calculations
- Adding conditional and custom columns
- Creating columns from examples
Reshaping Report-Style Data
Turn presentation-friendly reports into analysis-ready tables.
- Recognizing analysis-ready data versus report layouts
- Promoting and fixing column headers
- Removing subtotal and total rows
- Unpivoting columns into rows
- Pivoting rows into columns
- Transposing tables
- Summarizing data with Group By
- Grouping by dates and times
Combining Data from Multiple Sources
Bring related data together from multiple tables, files, and systems into a single, consistent table.
- Appending queries to stack similar tables
- Combining all files in a folder into one query
- Combining multiple sheets from a workbook
- Handling new or renamed columns in incoming files
- Merging queries to look up and join data
- Merging on multiple key columns
- Choosing join types: left outer, inner, full outer, and left and right anti joins
- Using fuzzy matching to merge inconsistent values
Building Reliable, Reusable Queries
Organize and troubleshoot queries so they keep working as data changes and can be maintained by others.
- Referencing vs. duplicating queries
- Staging queries and connection-only loads
- Viewing query dependencies
- Organizing queries into groups
- Deleting queries
- Using data profiling to check column quality and distribution
- Finding and fixing errors that break a refresh
- Creating parameters for file paths and filter values
- Reading and editing M code in the formula bar and Advanced Editor
- Creating a date table with Power Query
- Query folding basics for database sources
Power Query in Excel
Put queries to work in Excel with the right load and refresh settings, refreshable reports, and Copilot in Excel.
- Load options: table, PivotTable, connection only, and data model
- Refresh options: refresh on open, background refresh, and timed refresh
- Building a refreshable PivotTable report with slicers
- Exploring query results with Copilot in Excel
- Reviewing and validating AI-generated results
- Sharing workbooks that contain queries
Power Query in Power BI
Put queries to work in Power BI, from loading the model to publishing and scheduled refresh, and learn how to move work between Excel and Power BI.
- Loading queries into the Power BI model
- Enable load and include in report refresh settings
- Building a quick report from query output
- Publishing to the Power BI Service and scheduling refresh
- When a gateway is needed for on-premises data
- Power Query Online and dataflows: an overview
- Moving queries from Excel to Power BI
- Choosing when to use Excel, Power BI, or both
Excel course instructors
AGI instructors are Excel professionals and skilled teachers. You'll learn from a live Excel professional that brings years of experience that will help you learn Excel quickly and easily.

Grace
MS, Information Design
BA, Digital Communications
Adjunct Professor, St. Olaf

Shirley
MLA, Harvard
MS, Bentley
BS, Bentley

Elizabeth
BS, Finance
Teaching Assistant, Virginia Tech
Accounting & Finance Roles
Custom and private Excel classes
This Excel course is available as a private class. Curriculum can be customized for your specific needs. Excel classes can be delivered at your location, online, or in our classrooms. For more information, call 781-376-6044 to speak with a training consultant or contact us.
Power Query course prerequisites
This course requires knowledge of Excel equivalent to the topics in our introductory and intermediate Excel Course, including knowledge of formulas and functions, charts, formatting, and absolute cell references. A computer with Excel and Power BI Desktop is provided for use during the course for in-person training at our training center, or use your own computer with your own copy of Excel and the free Power BI desktop for live online courses.
You can attend this Power Query class with a live instructor online or in-person. You can also schedule this as a corporate training for your group or organization or as private training for yourself or your group.
Power Query Course materials
You will receive a Power Query course manual for this class.
Frequently Asked Questions for Power Query Course
Which Excel course covers Pivot tables and V LOOKUP?
You will learn Pivottables and VLOOKUP in the intermediate Excel course. These topics are covered in-depth with hands-on projects, and the ability to ask questions from a live instructor.
Prerequisites for this Excel course
Prior to attending this Excel classes, you should be familiar with all the concepts covered in the: Excel class - Intermediate. This can be through attending the course or through experience.
What to take after this Excel course
After you have attended this course, you may find it useful to continue your learning and attend: Power BI course - introduction.
