power query in excel 2026: the tool every analyst should use

by

power query in excel 2026: the tool every analyst should use

most Excel users have never opened Power Query. they spend hours doing find-and-replace, deleting blank rows, splitting columns by delimiter, and copy-pasting between sheets. the irony is that Power Query has been built into Excel for over a decade, ships free, and would automate roughly 80 percent of that manual work into a click.

Power Query is the most underused feature in Excel. once you learn it, you will look back at the manual cleanup days the way analysts who learned SQL look back at their pivot-table-only era. the leap is that big.

this tutorial is for analysts, solopreneurs, and small business operators who already use Excel daily and want to eliminate the repetitive cleanup that eats their afternoons. you will get a step-by-step walkthrough of the Power Query workflow, the 10 transforms that handle most real-world cleanup, and the patterns that turn a 90-minute manual process into a 30-second refresh. by the end you will have your first working query and a permanent shift in how you handle messy data.

what Power Query is

Power Query is a data-transformation engine built into Excel (and Power BI). it lets you import data from almost any source, clean and shape it through visual steps, and load the result into your workbook.

Power Query is Microsoft’s no-code data transformation tool, built into Excel since 2013 and free with every Excel license. it lets analysts clean, combine, and shape data through a visual step-by-step interface that records every transformation as repeatable instructions. for solopreneurs and small business analysts in 2026, it eliminates roughly 80 percent of the manual data-cleaning work that traditionally eats Excel-heavy workflows.

every transformation gets recorded. when the source data updates, you click Refresh and the same cleanup runs automatically.

what Power Query is great at

  • combining many CSV files into one table
  • cleaning inconsistent text (capitalization, spacing, formatting)
  • splitting and merging columns
  • pivoting and unpivoting (reshaping wide to long)
  • joining multiple tables
  • filtering rows
  • handling dates and number formats
  • pulling data from web pages (limited)
  • connecting to databases, APIs, and cloud sources

what Power Query is not built for

  • statistical analysis (use formulas, pivots, or Power Pivot)
  • visualization (use charts, Power BI, or Looker Studio)
  • machine learning (use Python, R, or specialized tools)
  • handling truly massive datasets (Excel still has the 1M-row limit)

for the BI and analysis layer that Power Query feeds, see Looker Studio complete tutorial 2026 and how to build a business dashboard.

prerequisites

  • Excel 2016 or later (Power Query is built in)
  • a CSV, Excel, or other data file to work with
  • 30 minutes for your first query

if you have Excel for Mac 2019 or later, Power Query is included. older Mac versions had limited support; upgrade if you can.

step 1: open the Power Query editor

three ways to start:

option A: from a file

  1. in Excel, click Data tab → Get Data → From File → From Text/CSV (or From Workbook for an .xlsx file).
  2. browse to your file.
  3. Excel previews the data. click Transform Data to open the Power Query editor.

option B: from a table on the current worksheet

  1. select your data table in Excel.
  2. click Data → From Table/Range.
  3. Excel converts the range to a table (if not already) and opens Power Query.

option C: from a web page

  1. Data → Get Data → From Other Sources → From Web.
  2. paste the URL.
  3. Excel scrapes any tables on the page and lets you pick.

[SCREENSHOT: Excel Data tab showing Get Data dropdown with options]

step 2: understand the Power Query editor layout

the editor opens in a new window. four main areas:

  • left panel: queries (you can have multiple)
  • center: data preview
  • right panel: query settings, including Applied Steps
  • top ribbon: transforms (Home, Transform, Add Column, View)

the Applied Steps panel on the right is where Power Query records every transformation. you can click any step to see the data at that point and undo or modify steps individually.

[SCREENSHOT: Power Query editor with applied steps panel highlighted]

step 3: promote headers if needed

Power Query usually detects headers automatically. if not:

  1. click Home → Use First Row as Headers.
  2. the first row becomes column names.

if your CSV has multiple header rows or junk rows above the data, remove them first via Home → Remove Rows → Remove Top Rows.

step 4: change column types

Power Query auto-detects types but often gets dates wrong, especially in international data.

  1. click the type icon (small ABC, 123, calendar) at the left of each column header.
  2. select the correct type: Text, Whole Number, Decimal, Date, etc.

correct types matter. a date stored as text cannot be filtered chronologically.

[SCREENSHOT: column type selector with options]

step 5: clean text columns

right-click any column header for cleaning options. the most common:

  • Trim: removes leading/trailing whitespace
  • Clean: removes non-printing characters
  • UPPERCASE / lowercase / Capitalize Each Word: text case
  • Replace Values: find and replace
  • Split Column: by delimiter, by number of characters, etc.
  • Extract: first/last N characters, text between delimiters

example: a Country column with ” USA “, “usa”, “U.S.A.” can be standardized in three steps:
1. Trim
2. Lowercase
3. Replace Values: “u.s.a.” → “usa”

step 6: filter rows

filter the same way you filter in Excel:

  1. click the dropdown arrow at the right of any column header.
  2. uncheck values you do not want, or use Number/Text/Date Filters for ranges.

example: filter to only 2026 data, or only orders above $100, or only specific products.

step 7: remove duplicates

  1. select the column(s) that should be unique.
  2. click Home → Remove Rows → Remove Duplicates.

if multiple columns are selected, only rows where the combination is duplicated are removed.

step 8: split a column

a common case: a Full Name column with “John Smith” needs to be split into First Name and Last Name.

  1. select the Full Name column.
  2. click Home → Split Column → By Delimiter.
  3. choose the space character.
  4. choose At Each Occurrence or At First Delimiter.

[SCREENSHOT: split column dialog showing delimiter options]

step 9: merge two queries

merging is Power Query’s term for joining. it combines two queries on a common key.

  1. with two queries loaded, click Home → Merge Queries → Merge Queries as New (or Merge Queries to merge into the current).
  2. choose the two tables.
  3. select the join key column from each.
  4. choose the join type:
join type meaning
Left Outer all rows from first table + matches from second
Right Outer all rows from second + matches from first
Full Outer all rows from both
Inner only matches
Left Anti rows in first that have NO match in second
Right Anti rows in second that have NO match in first

[SCREENSHOT: merge queries dialog with join type options]

  1. click OK. Power Query adds a new column with the related rows from the second table. click the expand icon at the column header to choose which fields from the second table to include.

step 10: append (stack) two queries

if you have multiple files with the same columns (e.g., monthly sales CSVs), append them.

  1. Home → Append Queries → Append Queries as New or Append Queries.
  2. select the queries to append.

result: rows from all queries stacked vertically into one table.

step 11: pivot and unpivot

pivot turns rows into columns. unpivot turns columns into rows.

example: you have monthly sales as columns (Jan, Feb, Mar…) and want them as rows.

  1. select the columns to unpivot.
  2. Transform → Unpivot Columns.
  3. result: two new columns (Attribute = the month name, Value = the sales number).

[SCREENSHOT: unpivot transform with before/after preview]

unpivoting is the secret to getting messy “wide” data into the “long” shape that pivot tables and dashboards prefer.

step 12: load the result into Excel

once your query is done:

  1. click Close & Load in the Home tab.
  2. choose where to load it: a new worksheet, the existing data model, or a connection only.

the result appears as a Table in Excel. when source data changes, right-click the table → Refresh.

[SCREENSHOT: close & load dropdown with destination options]

a worked example: combining 12 monthly CSVs into one analysis-ready table

a real workflow that Power Query makes trivial:

  1. Data → Get Data → From File → From Folder.
  2. select the folder containing your 12 CSVs.
  3. Power Query lists all files in the folder.
  4. click Combine → Combine & Transform Data.
  5. Power Query creates a sample query, applies it to all files, and stacks them into one table.
  6. clean as needed (types, trim, filter).
  7. close & load.

next month when a new file lands in the folder, click Refresh. the new file is automatically included. this is what eliminates the most-common Excel busywork.

comparing Power Query to alternatives

tool cost learning curve best for
Power Query (Excel) free with Excel medium Excel users, free baseline
Power Query (Power BI) free with Power BI Desktop medium Power BI users
Tableau Prep $75/month medium Tableau users
dbt free open-source high SQL-comfortable, scale
pandas (Python) free high scripting, ML pipelines

Power Query is essentially free if you already pay for Excel. for a comparison with the Tableau-side equivalent see tableau prep builder tutorial. for the SQL-based alternative see dbt for analysts.

common mistakes

1. doing manual edits in the loaded table

if you edit the loaded result in Excel, those edits get wiped on the next refresh. all edits should happen in the Power Query editor.

2. skipping the type-setting step

bad data types break formulas and pivots downstream. always set types explicitly.

3. building one massive query

queries with 50 applied steps become unmaintainable. break large queries into smaller ones that feed each other.

4. not naming queries and steps

the default names (“Query1”, “Removed Top Rows”) become incomprehensible later. rename queries and rename important steps.

5. ignoring the M language

Power Query’s underlying language is M. you do not have to write M, but learning to read it (Advanced Editor in the View tab) lets you debug and adapt queries faster.

advanced patterns that handle real data

once basics are comfortable, four patterns handle most messy real-world data.

pattern 1: combining many CSVs from a folder

shown earlier as a worked example, this pattern is the most-used real-world Power Query workflow. one folder, many files, one combined table that auto-grows when new files arrive.

key tip: name files consistently. the combine step uses filename patterns to detect structure.

pattern 2: pulling data from a web API

Power Query supports JSON web sources via Data → Get Data → From Other Sources → From Web. paste a URL that returns JSON; Power Query parses it and lets you expand fields.

example: pulling exchange rates from a public API daily. once configured, a Refresh button updates the latest rates.

pattern 3: transforming a poorly-formatted “report” into a real dataset

many corporate reports come as Excel files with merged cells, blank header rows, and totals scattered through the data. Power Query can reshape these into clean rectangular data:

  • Remove Top Rows for blank headers
  • Fill Down for merged-cell labels
  • Filter Rows to remove totals
  • Unpivot to fix wide formats

each step adds 30 seconds; the cleanup that took 30 minutes manually now takes 3.

pattern 4: parameterized queries

a parameter is a named value you can reference across queries. example: a parameter “Reporting Year” that you change once and all queries filter to the new year.

create parameters via Home → Manage Parameters → New Parameter.

useful for: monthly or quarterly recurring reports where the only change is the period.

the M language (just enough to be dangerous)

Power Query records every step as M code. you can view it via View → Advanced Editor.

example M code:

let
    Source = Csv.Document(File.Contents("C:\data\sales.csv")),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Typed = Table.TransformColumnTypes(Promoted, {{"Date", type date}, {"Amount", type number}})
in
    Typed

reading M is useful for debugging. writing M is useful when the GUI does not support what you need (e.g., dynamic column references).

most Excel users never write M directly. but reading it occasionally is a good skill to develop.

power query in Power BI vs Excel

Power Query in Power BI Desktop is functionally identical to the Excel version with a few extras (more connectors, more frequent updates, better performance on large data). if you outgrow Excel’s row limit (1.04 million rows), Power BI Desktop is the natural next step and your queries port over directly.

for the Power BI tutorial see the cluster-B Power BI walkthrough (Power BI Free Tutorial: Small Business Analytics in One Afternoon, when published).

connecting Power Query to your wider stack

Power Query is the cleaning layer. once data is clean:

a typical small-business analyst stack: source files → Power Query → Excel pivot tables / Looker Studio → decisions. Power Query is the load-bearing layer in the middle.

conclusion

Power Query is the highest-leverage skill any Excel user can learn. it ships free, eliminates roughly 80 percent of manual cleanup work, and makes data refreshes a one-click operation. analysts who learn it stop dreading the next data drop. analysts who do not keep losing afternoons to the same kind of cleanup over and over.

the 12 steps above cover the workflow that handles most real-world data prep: connect, type, clean, filter, dedupe, split, merge, append, pivot, unpivot, load. each step is a couple of clicks. the muscle memory builds in about 5 hours of practice.

start with one painful workflow this week. pick the one that takes you the longest manually each month. rebuild it in Power Query. close and load. next month, click refresh and watch the same work happen automatically. that single experience usually converts skeptics permanently.

if you only learn one of the tools in this tutorial series, learn this one. Power Query saves more solopreneur hours per week than anything else available for free in 2026.