How To Create a Query in Excel Power Query

We offer private, customized training for 3 or more people at your site or online.

Power Query is an add-in included in Microsoft Excel that helps users manipulate, transform, and reshape their data. Power Query allows users to connect to various data sources and then fetch, clean, and transform their data with ease.

Power Query comes with a graphical interface for getting data from sources and a Power Query Editor for applying transformations.

Extract, Transform, and Load (ETL)
Using Power Query, you can perform the extract, transform, and load (ETL) processing of data.

Advantages of using Power Query within Excel

  • The app is built into Excel and is free.
  • It can extract data from various sources including SQL, CSV, text, PDF, etc.
  • Power Query replaces the need to write advanced functions, including VLOOKUP, XLOOKUP, SUMIFS, etc.
  • It allows users to create relationships between data sets.
  • It remembers your cleanup steps and next tie runs them automatically, saving hours.
  • Power Query can join similar data from multiple data sets quickly and efficiently.

Let Accelebrate customize Excel Power Query training for your team of 3 or more.

How do I create a query in Excel that automates the cleaning up of my CSV file sent to me each month?

Creating such a query will ensure you no longer have to manually clean up a file in Excel.  The goal is to connect Excel to the CSV file and do all clean up work in Power Query.  To do this:

  1. Open a blank document in Excel.

  2. Start Power Query by going to Data → Get Data From File → From Text/CSV.

  3. Browse for the file you are connecting to and select Import.

    Import from file

    Load file

  4. Click on Transform Data (to get into Power Query).

  5. The Power Query Editor screen will appear.  You can then use the buttons on the ribbons or right+click on a column to transform the data in any way that you need to.  As you transform the data, steps are added in the Applied Steps pane.  These are the steps that will then automatically run on next month's data set.

    In this example, I needed to filter the data set to include 3 product categories (BX, CB, and CC); I clicked on the drop-down arrow on the PRODCATEGORYID field and filtered for the data.

    Filter data

  6. A Filtered Rows step was automatically created. 

    Filter rows

  7. The data is now transformed and ready to be loaded into Excel.  To load the data, click the Close & Load button.

    Close and load button

  8. The transformed data is now loaded into Excel and ready to be reported on.

  9. To load the new data next month, click the Query tab and select Edit to get back into the Power Query Editor.

  10. Double-click the Source step in the Applied Steps pane and browse for the new file and click OK.

  11. Click Close & Load to transform and load the new data.

Supercharge your team's productivity with Excel Training. We work with you to customize a course to teach your team how to use this powerful tool to simplify your workflow, boost efficiency, and unlock valuable insights from your data.



Holly FrenchWritten by Holly French, MOS Master.

For the past 30+ years, Holly has had a passion for software training, including instructor-led training, e-learning, instructional development, software development, software deployment, or just plain teaching a stranger how to use their computer.  Her true passion is to debunk traditional training methods and train in an organized, fun, personable, knowledgeable, logical, IMPACTFUL, and easy-to-understand way, incorporating the most efficient method for everyone. Each learner is different, each day is different, each software is different, but the goal is the same...More Tricks. Less Clicks.

Holly trains all levels of the Microsoft & Adobe products including Microsoft 365, SharePoint Online, Teams, Power BI, Power Automate, Planner, and more.  Holly is also a MOS (Microsoft Office Specialist) Master.

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