Power Query Introduction

Everything in this course so far has lived inside the Excel grid — formulas, functions, and dynamic arrays all working on data that already sits in cells. Power Query is something different. It is a dedicated data transformation engine built into Excel that sits one step upstream from the grid. Its job is to connect to data sources, clean and reshape that data, and load the result into Excel as a table — automatically, repeatably, and without a single formula. If you have ever spent an hour cleaning a CSV export by hand, or re-run the same copy-paste-reformat routine every month, Power Query is what eliminates that work entirely.

What Power Query Is — and What It Is Not

Power Query is not a formula. It is not a function you type into a cell. It is a separate editor — a point-and-click ETL tool (Extract, Transform, Load) — where you build a series of transformation steps on your data. Each step is recorded as a query. When you click Refresh, Power Query replays every step automatically on the latest version of the data and delivers the result into the grid.

Where to Find Power Query

Power Query lives on the Data tab. The main entry points are in the Get & Transform Data group. Once a query has been created it also appears in the Queries & Connections pane on the right side of the screen.

The Power Query Editor

When you connect to a data source, Power Query opens in its own editor window — separate from the Excel grid. This is where all transformation work happens. The editor has four main areas you need to know.

Your First Query — Importing a CSV File

The most common starting point is a CSV or text file export from another system — a payroll export, a CRM download, a sales report. Here is the full walkthrough from connection to loaded table.

Step 1 — Connect to the file
  Data tab → Get Data → From Text/CSV → browse to your file → Import

Step 2 — Preview and confirm
  Power Query shows a preview of the file. Check:
  - Are column headers on row 1? (if not → Transform → Use First Row as Headers)
  - Are data types correct? (numbers showing as text?)
  Click: Transform Data (to open the editor) or Load (to load directly)

Step 3 — Apply transformations in the editor
  Each action in the editor adds a step to Applied Steps.
  Common first steps:
  - Home → Use First Row as Headers
  - Click column header → Change Type (number, date, text)
  - Right-click column → Remove (to drop unwanted columns)
  - Home → Remove Rows → Remove Blank Rows

Step 4 — Load to Excel
  Home tab (in editor) → Close & Load → Load
  Options:
  - Table           (default — loads into an Excel Table in a new sheet)
  - PivotTable      (loads directly into a PivotTable)
  - Connection Only (keeps query available for Power Pivot / Data Model)

Step 5 — Refresh
  When source data changes: Data tab → Refresh All
  Or right-click the table → Refresh

Applied Steps — The Query Recipe

The Applied Steps panel is the core concept that makes Power Query so different from manual data cleaning. Every transformation you apply — filtering rows, changing a data type, removing a column — is recorded as a named step. The steps form a recipe that Power Query replays from top to bottom every time you refresh. You can reorder steps, delete steps, and click any step to inspect the data at that point. Nothing is destructive.

Power Query vs Formulas — Choosing the Right Tool

Power Query and Excel formulas solve some of the same problems but in fundamentally different ways and at different scales. Understanding when to use each saves a lot of time and avoids building the wrong solution.

Next up — Transforming Data in Power Query, where we go hands-on with the most important transformation operations: changing types, splitting columns, filtering, replacing values, and reshaping tables with pivot and unpivot.