Course Number: OFC-106
Duration: 2 days (13 hours)
Format: Live, hands-on

Excel Power Query Training Overview

This Excel Power Query training teaches attendees how to easily clean, transform, and load data into Excel. Participants learn how to import data from various sources and then clean and transform that data, including removing duplicates, removing blank rows, and converting text to numbers. This class is appropriate for Excel 2016, 2019, Online, and later.

Location and Pricing

Accelebrate offers instructor-led enterprise training for groups of 3 or more online or at your site. Most Accelebrate classes can be flexibly scheduled for your group, including delivery in half-day segments across a week or set of weeks. To receive a customized proposal and price quote for private corporate training on-site or online, please contact us.

In addition, we offer some courses as live, instructor-led online classes for individuals.

Objectives

  • Create queries from an Excel tab, a database, and a folder
  • Shape data
  • Merge and append data
  • Create calculations
  • Group and summarize data
  • Use the advanced editor
  • Use Power Query Data in Excel

Prerequisites

All attendees must have prior knowledge of Excel.

Outline

Expand All | Collapse All

Introduction to Power Query
  • What is Power Query?
  • Setting Up Power Query
  • Why use Power Query?
Demo Power Query
  • Setup QAT and demo Data-Get & Transform
  • Discuss Power Query Screen
  • Refreshing Queries
  • Editing Queries
Creating a Query from an Excel tab
  • Extracting Excel Table Data (when already opened)
  • Extracting Excel Named Range
  • Exercise
Creating a Query from a Database
  • Extracting from Access database
  • Extracting from other databases
Creating a Query from a Folder
  • Extracting Files from a Folder
  • Adding more Files to a Folder
Transforming (Shaping) Data
  • Data Types
  • Date Issues
  • Replacing Data
  • Transforming Data
  • Sorting Data
  • Moving Data & Deleting Data
  • Filtering Data
  • Splitting & Merging Data
Using Merge and Append
  • Append all tables in a spreadsheet
  • Append a new table into existing query
  • Merging Tables (NO MORE VLOOKUP!)
Unpivoting Data
  • Demo Pivoted Data
  • Unpivot Data
Creating Calculating Fields
  • Create New Calculated Column
  • Create If-Then-Else
  • Create an Autonumbering Column
  • Creating Other Calculations
  • Exercise
Grouping and Summarizing
  • Grouping Data
  • Summarizing Data
Other
  • Using the Advanced Editor (Power Query Formula Language)
  • Add Column from Examples (e.g., Flashfill)
  • Convert to List (deletes all other columns but selected)
  • Using the View tab
Using Power Query Data in Excel
  • Creating Pivot Tables
  • Using the Data Model
Conclusion

Training Materials

All Excel Power Query training attendees receive a course workbook.

Software Requirements

  • Excel installed from Microsoft Office Professional Plus or Microsoft 365
  • Related lab files that Accelebrate will provide


How do I use Power Query?

First, you will need to import your data into Excel. The Power Query Editor can then clean, transform, and load your data. The Power Query Editor is a graphical interface that makes it easy to edit your data, including:

  • Removing duplicates
  • Removing blank rows
  • Converting text to numbers
  • Merging tables
  • Grouping data
  • Calculating summary statistics

What are the benefits of using Power Query?

  • Power Query can pull data from a variety of sources
  • It has a variety of functions that allow you to clean and transform your data
  • Power Query is not difficult to use
  • It makes task automation easy

Free Excel Power Query Resources

Looking for tips and tricks at a glance? Check out our Excel Power Query Cheat Sheet.

Webinar: How to use Power Query with Microsoft Excel and Power BI
Get a thorough overview of Power Query and learn how to use Power Query within Excel and Power BI Desktop to save you time and money. 

Webinar: Introduction to Excel Power Query
Get a thorough overview of Power Query and watch a walkthrough of how to use Power Query within Excel.

Learn faster

Our live, instructor-led lectures are far more effective than pre-recorded classes

Satisfaction guarantee

If your team is not 100% satisfied with your training, we do what's necessary to make it right

Learn online from anywhere

Whether you are at home or in the office, we make learning interactive and engaging

Multiple Payment Options

We accept check, ACH/EFT, major credit cards, and most purchase orders



Recent Training Locations

Alabama

Birmingham

Huntsville

Montgomery

Alaska

Anchorage

Arizona

Phoenix

Tucson

Arkansas

Fayetteville

Little Rock

California

Los Angeles

Oakland

Orange County

Sacramento

San Diego

San Francisco

San Jose

Colorado

Boulder

Colorado Springs

Denver

Connecticut

Hartford

DC

Washington

Florida

Fort Lauderdale

Jacksonville

Miami

Orlando

Tampa

Georgia

Atlanta

Augusta

Savannah

Hawaii

Honolulu

Idaho

Boise

Illinois

Chicago

Indiana

Indianapolis

Iowa

Cedar Rapids

Des Moines

Kansas

Wichita

Kentucky

Lexington

Louisville

Louisiana

New Orleans

Maine

Portland

Maryland

Annapolis

Baltimore

Frederick

Hagerstown

Massachusetts

Boston

Cambridge

Springfield

Michigan

Ann Arbor

Detroit

Grand Rapids

Minnesota

Minneapolis

Saint Paul

Mississippi

Jackson

Missouri

Kansas City

St. Louis

Nebraska

Lincoln

Omaha

Nevada

Las Vegas

Reno

New Jersey

Princeton

New Mexico

Albuquerque

New York

Albany

Buffalo

New York City

White Plains

North Carolina

Charlotte

Durham

Raleigh

Ohio

Akron

Canton

Cincinnati

Cleveland

Columbus

Dayton

Oklahoma

Oklahoma City

Tulsa

Oregon

Portland

Pennsylvania

Philadelphia

Pittsburgh

Rhode Island

Providence

South Carolina

Charleston

Columbia

Greenville

Tennessee

Knoxville

Memphis

Nashville

Texas

Austin

Dallas

El Paso

Houston

San Antonio

Utah

Salt Lake City

Virginia

Alexandria

Arlington

Norfolk

Richmond

Washington

Seattle

Tacoma

West Virginia

Charleston

Wisconsin

Madison

Milwaukee

Alberta

Calgary

Edmonton

British Columbia

Vancouver

Manitoba

Winnipeg

Nova Scotia

Halifax

Ontario

Ottawa

Toronto

Quebec

Montreal

Puerto Rico

San Juan