Power Query in Excel — Complete Beginner’s Guide + Examples (2026)

If you spend Monday mornings deleting junk rows, fixing dates that came in as text, and copy-pasting twelve exports into one sheet — Power Query exists to make that work disappear. It is Excel’s built-in data preparation engine: you clean a messy file once, by clicking, and Excel records every move so it can replay the whole cleanup next week with a single Refresh. In 45 minutes you’ll learn where Power Query lives, how to import from CSV, web, and folders, the editor’s essential transformations, unpivoting, merging, appending, and the close-and-load refresh workflow. Works in Excel 2016 through Microsoft 365 — nothing to install.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is Power Query?

Power Query is Excel’s built-in tool for getting data from somewhere, cleaning it up, and loading the tidy result into your workbook — what data professionals call ETL (extract, transform, load), wrapped in a point-and-click interface. It connects to almost anything: CSV and text files, other workbooks, whole folders of files, web pages, SQL databases, SharePoint lists, and dozens more sources. You shape the data in a dedicated window called the Power Query Editor, and the finished table lands in a worksheet ready for pivot tables, charts, and formulas.

The idea that makes Power Query special is the Applied Steps list. Every transformation you perform — remove a column, filter out blanks, change a type, unpivot — is recorded as a named step in order. The steps are not a one-off edit; they are a recipe. Next month, when the source file has new rows, you do not redo anything. You click Refresh and Power Query re-reads the source and replays the recipe from top to bottom in about a second. The hour of manual cleanup you used to do every reporting cycle becomes a single click, forever.

Under the hood, each step is one line of a language called M, written for you automatically as you click. You never have to learn M to be productive — this entire guide is click-driven — but it is reassuring to know the recipe is real code you can inspect, copy between workbooks, and tweak later. Power Query is also the exact same engine inside Power BI, so every skill here transfers directly if you ever move up to dashboards at scale.

One mental model to carry through this guide: Power Query prepares, pivot tables analyse. Power Query’s job ends when the data is clean and tidy — one row per record, one column per field, correct types, no junk. Summarising that clean data is the job of a pivot table or your formulas. Teams that adopt this split find their workbooks become dramatically simpler, because the cleanup logic stops being smeared across helper columns and lives in one inspectable place instead.

Before You Start

Power Query needs almost no setup, but three things will save you friction.

Check your Excel version

Excel 2016, 2019, 2021, and Microsoft 365 all have Power Query built in on the Data tab — look for the Get & Transform Data group. Excel 2010 and 2013 need the free legacy add-in from Microsoft (it appears as its own ribbon tab once installed). On Mac, Microsoft 365 includes Power Query with most common connectors; older Mac versions do not.

Know where your source data lives

Power Query connects to the source by path or URL, so a stable location matters. A CSV that lives in C:\Reports\sales.csv today and your Downloads folder tomorrow will break the refresh. Park recurring source files in one folder that does not move — refreshes then keep working for years.

Keep raw data raw

Power Query never modifies the source, and you should preserve that property in your workflow: do not edit the loaded output table by hand either. Any manual edits are wiped on the next refresh. All cleanup belongs in the query steps; all analysis belongs in formulas or pivot tables pointing at the output table.

1.Step 1 — Find Power Query on the Data Tab

Open Excel and click the Data tab. On the far left you’ll see the Get & Transform Data group — that group is Power Query. The main entry point is the Get Data dropdown, which lists every source Excel can connect to, organised into From File, From Database, From Azure, From Online Services, and From Other Sources.

Three shortcuts sit beside it for the most common cases: From Text/CSV, From Web, and From Table/Range. To the right, the Queries & Connections button opens a pane listing every query in the workbook — keep this open while you work; it is your query control panel.

You will also use two ribbon buttons constantly once queries exist: Refresh All (replays every query) and, inside the Queries & Connections pane, right-click options to edit, duplicate, or delete a query. If you have never used any of this before, that is normal — Microsoft’s own research found Power Query is one of the most under-discovered features in Excel despite shipping in every copy since 2016.

2.Step 2 — Import Data: CSV, Web, and Folder

Power Query has 40+ connectors, but three cover the vast majority of real work.

From a CSV or text file

Choose Data → Get Data → From File → From Text/CSV and pick the file. Excel shows a preview dialog where it has already guessed the delimiter, the file encoding, and the data types of each column. Now the most important decision in Power Query: the dialog offers Load and Transform Data. Always click Transform Data. Load dumps the raw file into a sheet as-is; Transform Data opens the editor so you can clean it first — which is the entire point.

From the web

Choose From Web, paste a URL, and Power Query scans the page for HTML tables. A Navigator dialog lists every table it found with a preview pane — tick the one you want and click Transform Data. This works beautifully for reference data like exchange rates, public statistics, or league tables, and the refresh re-scrapes the live page.

From a folder (the killer feature)

Choose From Folder and point at a folder of files with the same structure — say, one CSV export per month. Power Query lists the files, and clicking Combine & Transform Data stacks every file into one long table automatically, adding a Source.Name column so you know which file each row came from. Drop a new month’s file into the folder, hit Refresh, and it is absorbed with zero clicks. This single connector replaces hours of copy-paste consolidation per month for most finance teams.

3.Step 3 — Tour the Power Query Editor

Clicking Transform Data opens the Power Query Editor — a separate window with four regions you should be able to name:

The data preview (centre). A scrollable grid showing the first ~1,000 rows of your data as it stands after the currently selected step. It is a preview, not the data itself — nothing loads into Excel until you Close & Load. Each column header shows a small type icon (123 for whole numbers, ABC for text, a calendar for dates) and a filter dropdown.

The ribbon (top). Home holds the everyday commands (Remove Columns, Keep/Remove Rows, Split Column, Merge and Append). Transform changes columns in place (change type, format text, unpivot, replace values). Add Column creates new columns (custom formulas, conditional columns, dates parts). The Transform/Add Column distinction trips up beginners: the same command often appears on both tabs — one overwrites, the other adds.

The Queries pane (left). Every query in the workbook. You can stage work across multiple queries — e.g., a raw import query that two cleaned queries both reference.

The Applied Steps list (right). The recipe. Each action you take appends a step; click any step to time-travel the preview to that point; click the ✕ to delete a step; click the gear icon to edit a step’s settings. The formula bar above the grid shows the single line of M behind the selected step. If a step goes wrong, delete it — nothing is ever destructive.

4.Step 4 — Clean the Data: Remove Columns, Filter Rows, Set Types

Three transformations do 80% of all cleaning work. Practise them until they are reflexive.

Remove columns you don’t need

Click a column header (Ctrl+click for several) and press Delete, or right-click → Remove Columns. Even better is the inverse: select the columns you want and choose Remove Other Columns — this is robust against the source growing new junk columns later, because anything unexpected is automatically dropped on refresh.

Filter out junk rows

Open a column’s filter dropdown — it works like an Excel auto-filter but is recorded as a step. Untick (null) to remove blanks; use Text Filters or Number Filters for conditions like “does not begin with Total” or “greater than 0”. For structural junk, Home → Remove Rows offers Remove Top Rows (report headers above the real table), Remove Blank Rows, and Remove Duplicates. Home → Use First Row as Headers promotes the first data row into proper column names.

Set the data type of every column

Click the type icon in each column header and choose the right type: Whole Number, Decimal Number, Date, Text, etc. This matters enormously — a “date” column typed as Text will not sort chronologically, won’t group in pivot tables, and will break date arithmetic. Power Query guesses types on import, but always verify, especially with UK/US date formats: right-click a date column → Change Type → Using Locale… to tell Power Query that 03/04/2026 means 3 April, not 4 March.

5.Step 5 — Unpivot Cross-Tab Data

The most valuable single transformation in Power Query. Humans like wide data — products down the side, one column per month. Excel’s analysis tools like tall data — one row per product-month observation. Converting wide to tall by hand is miserable; in Power Query it is two clicks.

Select the identifier column(s) — the ones that should stay as columns, like Product — then go to Transform → Unpivot Columns → Unpivot Other Columns. Every remaining column collapses into two new ones: Attribute (the old column header — Jan, Feb, Mar…) and Value (the cell contents). Rename them to something meaningful like Month and Sales, fix the types, and you have tidy data.

Why prefer Unpivot Other Columns over plain Unpivot Columns? Future-proofing. If next year’s file gains a 13th month column, “Unpivot Other” automatically includes it on refresh, while a hard-coded list of columns would silently miss it. Always anchor on the columns that are stable and unpivot whatever else appears.

Once unpivoted, a pivot table can re-pivot the data any way you like — by month, by quarter, by product — which is exactly why tall is the right storage shape: it can become any wide layout on demand, while a wide layout is stuck as itself.

6.Step 6 — Merge Queries (Joins)

Merge is Power Query’s answer to VLOOKUP — it joins two tables on a shared key, but at table scale, with no formulas to drag and no #N/A scattering through your sheet.

Suppose a Sales query has a ProductID column and a Products query maps ProductID to names and categories. In the editor select the Sales query, then Home → Merge Queries. In the dialog: pick Products as the second table, click the ProductID column in both previews to set the key, and choose the Join Kind:

After OK, the merged table appears as a single column of nested Table values. Click the expand icon (⇅) in its header, tick just the columns you need (untick “Use original column name as prefix” for cleaner names), and they spread out as real columns. The dialog also tells you how many rows matched — an instant data-quality check that VLOOKUP never gives you.

7.Step 7 — Append Queries (Stacking)

Where Merge joins tables side by side, Append stacks them on top of each other. Twelve monthly sales exports with identical columns become one long table; this year’s data joins last year’s; the UK file stacks under the EU file.

With one of the queries selected, choose Home → Append Queries (to add rows into the current query) or Append Queries as New (to create a combined query and leave the originals untouched — usually the better choice). Pick “Two tables” or “Three or more tables”, select the queries, and click OK.

Append matches columns by name, not position. Columns that exist in one table but not another are kept, with null filling the gaps — so a renamed column (“Sales” vs “Sales Amount”) does not error, it quietly creates two half-empty columns. After any append, scan the column list for unexpected near-duplicates; if you find them, rename columns to match before the append step.

For recurring multi-file consolidation, remember Step 2’s From Folder connector does append automatically — prefer it whenever the files live together; use manual Append when combining queries from different sources (a database table plus a CSV, say).

8.Step 8 — Close & Load, then Refresh

When the preview looks right, send the result back to Excel with Home → Close & Load. The plain button loads to a new worksheet table; the dropdown’s Close & Load To… gives you the full choice:

Now the payoff. Next week the source file has new rows. You do not reopen the editor. You right-click the output table and choose Refresh, or press Ctrl+Alt+F5 (Refresh All) to update every query in the workbook. Power Query re-reads the source, replays every applied step in order, and rewrites the table — the report that used to take an hour of cleanup now takes one keystroke.

For hands-off refreshes, open Data → Queries & Connections → right-click the query → Properties and tick Refresh data when opening the file. Anyone who opens the workbook then always sees current data.

Try It: Query Step Builder

This is the Power Query workflow in miniature. Below is a deliberately messy sales table — blank rows, an amount column typed as text, and months spread across columns. Click the transformation buttons in any order and watch the table reshape live, while the Applied Steps list on the right records your recipe exactly as the real editor would. Reset and try a different order — notice that in this dataset, the result converges on the same tidy table.

Advanced Power Query Tips

Add a custom column with a formula

Add Column → Custom Column lets you compute new fields in M, e.g. [Units] * [Price] for revenue, or if [Amount] > 1000 then "Large" else "Small" for banding. For simple if/then logic, Conditional Column builds the same thing through a dialog with no code at all.

Parameterise the file path

Hard-coded paths break when workbooks move. Create a parameter (Home → Manage Parameters) holding the folder path, reference it in the Source step, and relocating the data becomes a one-field edit instead of editing every query. Teams often point the parameter at a cell in the workbook so non-technical users can retarget the query.

Group By: aggregation inside the query

Transform → Group By summarises before loading — total sales per region, row counts per customer. If the workbook only ever needs the summary, grouping in the query keeps the file small and fast, and the detail stays safely in the source.

Peek at the M code

Home → Advanced Editor shows the whole recipe as a single M script. Even if you never write M, reading it teaches you what your clicks actually did, and copying the script is the fastest way to clone a complex query into another workbook.

From clean data to a live dashboard

Power Query solves the cleanup; sharing the result is the next bottleneck — emailing workbooks around defeats the automation. Upload your cleaned table to DataHub Pro and it becomes an interactive, always-current dashboard with charts and forecasting built in: one link to share instead of attachment ping-pong.

Worked Example: Twelve Messy CSVs to One Clean Table

Here is the full workflow on a realistic case. A sales ops team receives one CSV per month in C:\Reports\2026\. Each file has two junk header rows, a TOTAL footer row, an Amount column with thousands separators stored as text, and months of history pivoted across columns.

1. Connect. Data → Get Data → From File → From Folder → C:\Reports\2026\ → Combine & Transform Data. Power Query stacks all twelve files and adds Source.Name.

2. Structural cleanup. Home → Remove Rows → Remove Top Rows (2) kills the junk headers; Use First Row as Headers promotes the real ones; a text filter on the first column — does not equal TOTAL — removes footers from every file at once.

3. Types. The Amount column shows the ABC text icon. Change Type → Decimal Number; Power Query strips the thousands separators automatically. The Date column gets Change Type → Using Locale → Date / English (United Kingdom) so day-first dates parse correctly.

4. Reshape. Select the Product and Date columns, Transform → Unpivot Other Columns; rename Attribute → Channel and Value → Sales.

5. Enrich. Home → Merge Queries against a Products reference query on ProductID (Left Outer), expand just Category and Margin.

6. Load. Close & Load To… → Table, then build a pivot on the loaded table.

Eleven applied steps, built once in roughly fifteen minutes. The monthly routine is now: drop the new CSV in the folder, open the workbook, Ctrl+Alt+F5. Total monthly effort: about four seconds — against the two hours the manual version took. Over a year that recipe pays for itself roughly a hundred times over, and unlike a macro it is inspectable step by step and never needs “enable content” security prompts.

Common Errors & Fixes

“DataSource.Error: could not find the file”

The source moved or was renamed. Open the query, click the Source step’s gear icon, and repoint the path — or use Data → Get Data → Data Source Settings → Change Source. Parameterising the path (Advanced tips) prevents this class of error entirely.

“Expression.Error: the column ‘X’ of the table wasn’t found”

A step references a column the source no longer provides — usually someone renamed a header in the source file. Click through the Applied Steps to find the first one that errors, then either fix the source header or edit the step (gear icon / formula bar) to the new name.

Numbers loaded as text (left-aligned, won’t sum)

A type step is missing or happened before a cleanup that re-broke it. Add a Change Type step after all text cleanup (trims, replaces). If values contain currency symbols or odd separators, use Transform → Replace Values to strip them first, then set the type.

Refresh is slow

Filter and remove columns as early in the steps as possible so less data flows through later steps. Against databases, early filters often “fold” into the source query so the server does the work. Also avoid loading huge staging queries to worksheets — set them to Connection Only.

Loaded table shows old data

Queries do not auto-refresh by default — the table shows whatever was loaded last. Refresh manually (Ctrl+Alt+F5) or enable refresh-on-open in Query Properties.