Excel Power Query


Course Number: OFC-106

Duration: 2 days (13 hours)

Format: Live, hands-on

Excel Power Query Training Overview

This Excel Power Query training course teaches attendees how to rapidly create powerful queries that select data from Excel, databases, directories of files, and a wide array of other data sources. This class is appropriate for Excel 2016, 2019, Online, and later.

If you missed our live free 1-hr webinar on Excel Power Query, you can still watch the recording!

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.

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 and Access installed from Microsoft Office Professional Plus or Office 365.
  • Related lab files that Accelebrate provides.
  • Please contact us for detailed setup instructions if you have purchased this class.
  • For classes delivered online, all participants need either dual monitors or a separate device logged into the online session so that they can do their work on one screen and watch the instructor on the other. A separate computer connected to a projector or large screen TV would be another way for students to see the instructor's screen simultaneously with working on their own.


Free Excel Power Query Webinar from Accelebrate:

MOS (Microsoft Office Specialist) Master Holly French gives a thorough overview of Power Query and demonstrates 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