Power Query in Excel: Get and Transform Data - v16 (Course Id 2807)

• Who is this course for?
  This course is designed for CPAs, accountants, and other professionals seeking Continuing Professional Education (CPE), particularly those with intermediate to advanced Excel knowledge.
• What is this course about or what problem does this course solve?
  This course focuses on using Power Query (Get & Transform Data) in Excel to efficiently retrieve, clean, transform, and combine data from multiple sources, helping users streamline data preparation tasks.
• How can the knowledge from this course be used?
  The knowledge can be used to automate data import and transformation processes, reduce manual errors, and improve efficiency in analyzing and managing large or complex datasets within Excel.
• Why is this course important to a CPA or Accountant?
  This course is important because it enables CPAs and accountants to handle data more efficiently, saving time and improving accuracy in financial analysis, reporting, and decision-making.
• When is this course relevant or timely?
  This course is especially relevant for professionals using Excel 2016 or later, including Microsoft 365, and reflects updates through February 2026, making it timely for current Excel users.
• How is a course like this consumed or used?
  This course is completed as a self-study text-based program, where participants read the material, work through examples and exercises, and pass a final exam to earn CPE credits.

Power Query is an Excel feature available within the newest versions of Excel. It used to be an add-in feature called Power Query. Microsoft calls it Power Query as well as Get and Transform and it  is a business intelligence tool  that allows you to retrieve data from a variety of sources, clean it and then transform or refine the data as needed. For example, you can import a database file, parse data, split or combine columns, change data types, group data and more. You can then take that transformed data back into Excel and analyze it using your preferred methods. What makes this feature exciting is that Excel records all your steps so  the data in the file can automatically be refreshed with Excel retrieving the source data, applying the same steps and updating the file for you. This is a huge time saver and will also reduce errors. 
Version: This feature is only available in Excel versions starting with Excel 2016.  To see if you have this feature, simply click on the Data tab in Excel. You should see a section located at the beginning of the ribbon called Get & Transform. If you do not see this section, then you do not have the Get & Transform Data feature and will not be able to do any of the exercises in this eBook. If you have the older add-in feature called Power Query you should be able to follow along just fine.  
Update: This course is based upon Microsoft 365 and updates through February 2026. Depending upon your version, some of the screenshots and commands may differ.

NASBA QAS - Text - NASBA Registry - 2807

Course Learning Objectives

• Recognize when to use the Power Query feature and how it transforms data
• Identify the different parts of Power Query
• Identify and recognize how to clean and refine data using Power Query
• Recognize different data types
• Distinguish different methods of Joining tables together
• Recognize how to combine and append tables or files

Chapter 1 - Power Query in Excel: Get and Transform Data

Course Learning Objectives

Overview

Versions

Get & Transform Data

Connect

Transform

Combine

Manage

Review Questions

Importing Data Basics

Importing a Text File & Transforming It

Clean and Transform Data

Clean Data

Transform Data

Data Type

Column Headings

Split Columns

Group Data

Sort Data

Review Questions

Query Settings

Exercise - On Your Own

Review Questions

More Importing – TRY IT!

Close and Load

Combining Files

Merging Data

Optional Exercise – On Your Own

Adding a 3rd table

Joins

Join Example

Review Questions

Appending Data

Unpivot

Summary

Links for Spreadsheets

Glossary