Excel Power Query: The Complete Guide (2026)
Home›Excel›Power Query
ExcelPillar Guide⏱ 22 min read

Excel Power Query: The Complete Guide

Power Query is the most important Excel skill Microsoft has added in the past decade. It replaces manual copy-paste, fragile VLOOKUPs, and one-off cleaning scripts with a repeatable, refreshable, visual data pipeline. This guide covers what Power Query is, why it matters, and how to use it end-to-end.

What Power Query actually is

Power Query is Excel's data-prep engine. It sits between your source data and the worksheet where you'll analyze it. Every action you take — filtering rows, splitting columns, unpivoting, merging — is recorded as a step. The result is a query that runs the same transformations every time you refresh.

Three things make Power Query different from formulas or manual work:

  1. Non-destructive. The source data is never modified. All transformations happen in the query, not in the source.
  2. Repeatable. Right-click a query → Refresh, and every step runs again against the current source. New data flows through the same pipeline.
  3. Explicit. Every step is visible in the query editor. You can see, edit, or remove any transformation.
The mental model shift

Traditional Excel: you own the destination cells and formulas. Power Query: you own the process — the sequence of steps that produces the destination. When source data changes, you don't redo the work; you refresh.

Finding Power Query in Excel

Ribbon path: Data → Get Data → From File / From Database / From Web / …

The Get Data dropdown lists every source type Power Query can connect to. For quick wins, three sources cover 80% of use cases:

  • From File → From Text/CSV — CSVs, TXT files
  • From File → From Workbook — other Excel files
  • From File → From Folder — every file in a folder, appended together (powerful)

Version notes: Power Query is native to Excel 2016 and later on Windows. Excel 2010 and 2013 need the free Power Query add-in from Microsoft. Excel for Mac has Power Query in Microsoft 365 subscriptions but with fewer connectors.

Connecting to data

From a CSV file

  1. Data tab → Get Data → From File → From Text/CSV
  2. Pick the file → Import
  3. Preview appears. Click Transform Data to open the editor (recommended), or Load to bring it in as-is.

From a folder (combining files)

The most valuable connector. Point Power Query at a folder, and it treats every file inside as rows of a single output table — perfect for monthly reports, log files, or exported extracts.

  1. Data tab → Get Data → From File → From Folder
  2. Browse to the folder → OK
  3. Click Combine → Combine & Transform Data
  4. Pick the sheet/table to combine from → OK

Now every file in that folder is stacked into one output. Drop a new file into the folder, refresh, and it appears.

From a database

SQL Server, PostgreSQL, MySQL, Oracle, and dozens of others are supported. You provide the server, database, and credentials once. Power Query stores the connection; you only need to refresh from then on.

From the web

Point Power Query at a URL — it can parse HTML tables directly. Great for reference data (currency codes, holiday lists, statistics tables published as HTML).

Transforming data

The Power Query Editor opens automatically when you choose "Transform Data" during import. This is where the work happens. The editor has three main areas:

  • Left — Queries pane (list of all queries in the workbook)
  • Center — Preview of the current query's data
  • Right — Applied Steps (the recorded transformations)

Every action you take in the editor adds a step. Steps are stored in order and run sequentially when the query refreshes.

Common transformations

Filtering rows

Click a column's filter arrow. Uncheck values to exclude, or use text/number filters ("begins with", "greater than", etc.). Adds a "Filtered Rows" step.

Removing columns

Select column → right-click → Remove. Or: Home tab → Choose Columns to bulk-select.

Renaming columns

Double-click the header. Type the new name.

Changing data types

Ribbon path: Home → Data Type dropdown, or click the type icon next to the column name

Critical step. Power Query guesses types on import — often wrong for dates or numbers. Explicitly set types early to catch parsing errors.

Splitting columns

Right-click column → Split Column → By Delimiter / By Number of Characters / By Position. Common for parsing "First Last" name fields or breaking apart concatenated codes.

Merging columns

Select multiple columns → Transform tab → Merge Columns. Pick a separator (space, comma, custom). Result is a single combined column.

Adding a column from formula

Add Column tab → Custom Column. Write a formula in M (see The M Language section).

Unpivoting columns

The killer feature. Turns wide data (columns Q1, Q2, Q3, Q4) into tall data (columns: Quarter, Value). Essential for PivotTable-ready data.

Ribbon path: Select columns to unpivot → Transform → Unpivot Columns

Grouping and aggregating

Transform tab → Group By. Similar to a PivotTable but built into the query. Group by one or more columns; aggregate with Sum, Count, Average, Min, Max, or custom operations.

Combining tables

Append (stacking)

Adds rows of one query beneath another. Both queries need the same or overlapping column structure. Home tab → Append Queries.

Common use: monthly extracts sit in separate tables — append them into a single historical dataset.

Merge (joining)

Combines two queries side-by-side based on a matching column. Similar to VLOOKUP or a SQL JOIN, but more powerful and refreshable.

Ribbon path: Home → Merge Queries → Merge Queries / Merge Queries as New

Choose the join type: Left Outer (default, keeps all rows from the first query), Inner (only matched rows), Right Outer, Full Outer, Left Anti (rows in first that aren't in second — great for finding "missing" records), Right Anti.

Loading results

Once your query is ready, close the editor. You'll be prompted:

  • Close & Load — Loads as a table in a new worksheet. Default.
  • Close & Load To… — Choose destination: Table, PivotTable, PivotChart, Data Model, or Connection Only.

Load destinations

  • Table — Regular Excel table on a sheet. Best for smaller datasets under a million rows.
  • PivotTable — Skips the table step. Loads directly into a PivotTable.
  • Data Model (Power Pivot) — Loads into the memory-efficient Data Model. Required for datasets over a million rows, or when you need to build DAX measures. See the Power Pivot guide.
  • Connection Only — No output. Used when a query is an intermediate step feeding into other queries.

Refreshing queries

Once loaded, the query is a living link to its source. Refresh options:

  • Manual: Data tab → Refresh All (refreshes every query). Or right-click a table → Refresh (that query only).
  • Auto on open: Query Properties → check "Refresh data when opening the file".
  • Periodic: Query Properties → check "Refresh every N minutes". Useful for dashboards on a shared drive.

The M language (briefly)

Behind the scenes, every step you record generates M code. The editor's Formula Bar shows the current step's M expression. To see the full script: View → Advanced Editor.

let Source = Csv.Document(File.Contents("C:\data\sales.csv")), Promoted = Table.PromoteHeaders(Source), Filtered = Table.SelectRows(Promoted, each [Region] = "West"), Sorted = Table.Sort(Filtered, {"Date", Order.Descending}) in Sorted

You don't need to write M by hand for most work — the UI generates it. But knowing the basics helps for two reasons:

  • You can edit steps directly for tweaks the UI doesn't expose
  • You can write custom columns in M for logic that isn't a click

See the M Language Guide for a proper introduction.

Common patterns

Monthly data appended from a folder

  1. Save all monthly extracts (sales-2026-01.xlsx, sales-2026-02.xlsx, etc.) in one folder.
  2. Get Data → From File → From Folder.
  3. Combine & Transform.
  4. Load to a table or PivotTable.

Drop a new month's file into the folder → Refresh → new data appears.

Cleaning a report with junk headers

Reports often have 3-4 header rows before the actual data starts.

  1. Import as normal.
  2. Home → Remove Rows → Remove Top Rows → enter the count.
  3. Home → Use First Row as Headers.

Reshaping wide to tall

Given Q1-Q4 columns with values, get a "Quarter" and "Value" pair:

  1. Select non-quarter columns (region, product, etc.).
  2. Transform → Unpivot Other Columns.
  3. Rename the "Attribute" column to "Quarter".

Adding a lookup table

Instead of a VLOOKUP, use Merge Queries. Bring both tables into Power Query, merge, expand the columns you need. Refreshable and doesn't clutter formulas.

Pitfalls to avoid

Renaming source columns breaks queries

Power Query references columns by name. If the source file changes a column header ("Amount" → "Amt"), the query fails at the step where it referenced "Amount". Fix: either rename the column back in the source, or edit the query's steps to use the new name.

File paths break when the workbook moves

Absolute paths (C:\Users\...) don't survive being emailed or moved. Use relative paths where possible, or store the folder path in a workbook cell and reference it in the query.

Not setting data types explicitly

Power Query auto-detects types from the first few hundred rows. If later rows have different formats, refresh fails. Explicit type conversion catches issues immediately.

Name your steps

Renaming applied steps ("Filtered West" instead of "Filtered Rows") makes queries readable when you come back in 6 months. Right-click a step → Rename.

Power Query vs. formulas vs. VBA

TaskBest toolWhy
Import CSV, clean, loadPower QueryRefreshable, visual, no code
Combine 50 files into onePower QueryFrom Folder connector — one click
Row-level calculationsFormulasImmediate, in the worksheet
Complex UI automationVBAPower Query can't drive Excel UI
Reshape wide→tall dataPower QueryUnpivot is impossible with formulas
Joining large datasetsPower QueryFaster than millions of VLOOKUPs
Excel Wizard

Power Query, without the learning curve

Power Query is powerful but has a real learning curve. Excel Wizard generates queries from plain-English descriptions ("import all CSVs from this folder, keep only the West region, unpivot the quarter columns"). The query and every applied step are ready to review, tweak, and load.

Install Excel Wizard →

Frequently asked questions

What is Power Query in Excel?

Power Query is Excel's built-in data prep tool. It connects to sources, lets you clean data through a visual editor, and loads the result back into Excel. Every step is recorded and can be refreshed.

Where is Power Query in Excel?

Data tab → Get Data. Native to Excel 2016+ on Windows. Available in Microsoft 365 for Mac with fewer connectors. Excel 2010/2013 need the free Power Query add-in.

Is Power Query free?

Yes — built into every modern Excel at no extra cost. Also powers Power BI, Power Automate, and Analysis Services.

What's the difference between Power Query and Power Pivot?

Power Query gets and cleans data. Power Pivot models and analyzes it. Typical workflow: Power Query imports → Data Model → Power Pivot builds relationships and DAX measures.