Power Query for People Who Refuse to Learn Power Query

Every Monday you open the same export, delete the same junk rows, split the same column, and fix the same dates. You have done it so many times you could do it asleep — which is exactly the problem, because you are doing work a machine should do. Power Query is the tool built for this, it has been sitting in your Excel ribbon since 2016, and you can get real value from it knowing about six clicks. No M code, no VBA, no course required.

What Power Query Actually Is (and Isn’t)

Power Query is a recorder for data cleanup. You connect to a source (a CSV, a workbook, a folder, a database), click through the cleanup steps once, and Excel writes them down as a repeatable recipe. Next week, when the new export arrives, you click Refresh and the entire recipe replays in about a second.

Three things make it different from everything else you have tried:

• It never touches the source file. Your raw export stays raw. The cleaned version lands on a new sheet.
• Every step is undoable and editable. Each click becomes a line in an “Applied Steps” list you can delete or reorder.
• It does the work once. Formulas recalculate constantly; a query runs only when you refresh.

It is not a replacement for analysis. Power Query gets data clean and shaped; the thinking still happens in your worksheet, usually in a pivot table.

When Power Query Beats Formulas

You do not need Power Query for everything. Here is the honest dividing line:

Task	Stick with worksheet tools	Use Power Query
One-time text split	Text functions or splitting, Flash Fill	Overkill
Same cleanup every week/month	Tedious, error-prone	Yes — this is the sweet spot
Combine 2 sheets	Copy-paste is fine	Optional
Combine 30 files in a folder	Hours of copy-paste	Yes — minutes, then automatic
Cross-tab data that should be a flat list	TRANSPOSE gymnastics	Yes — Unpivot does it in one click
Files too big to open comfortably	Excel chokes	Yes — it filters before loading

Rule of thumb: if you will do the cleanup more than twice, or the data arrives in more than one file, Power Query wins.

The Worked Example We’ll Follow

Say accounting sends you Sales_January.csv every month. It looks like this:

Rep - Region	Product	Week1	Week2	Week3	Week4
Smith - East	Widgets	1,200	980	1,410	1,150
Chen - West	Gadgets	760	810	0	905

Two classic problems: the rep and region are jammed into one column, and the weeks run across columns instead of down rows (which breaks pivot tables and lookups). We will fix both, once, forever.

Step 1: Get Data

Go to Data → Get Data → From File → From Text/CSV, pick the file, and click Transform Data (not Load — Transform opens the editor). This beats opening the CSV directly in Excel because Power Query won’t mangle leading zeros or auto-convert text to dates on import; you control the types.

The same menu connects to Excel workbooks, folders, web pages, and databases. The skill transfers: learn it once for CSVs and you know it for everything.

Step 2: Meet the Editor (It’s Just Four Areas)

The Power Query Editor window looks intimidating and is not. Four things matter:

1. The data preview in the middle — first 1,000 rows of your data.
2. Applied Steps on the right — your recorded recipe. Click any step to time-travel; click the X next to a step to undo it. This is the safety net: you cannot break anything permanently.
3. The ribbon on top — Home and Transform tabs hold 90% of what you need.
4. Column type icons in each header — 123 is a whole number, ABC is text, the calendar icon is a date. Click the icon to change a type. Fixing types here solves most of the date-format chaos you would otherwise fight cell by cell in the worksheet.

Nothing you do here changes the worksheet until you click Close & Load.

Step 3: The Four Transforms That Cover 90% of Cleanup

Split a column

Select the Rep - Region column, then Home → Split Column → By Delimiter. Power Query suggests the delimiter (here, -) automatically. Click OK and you get two columns; double-click each header to rename them Rep and Region. Unlike Text to Columns, this re-applies itself on every refresh — no redoing it next month.

Merge columns

The reverse operation. Select two or more columns (Ctrl+click the headers), then Transform → Merge Columns, pick a separator, done. The worksheet equivalents — the & operator or TEXTJOIN — still make sense for ad-hoc jobs, but in a recurring report the query version maintains itself.

There is a second, more powerful “merge” hiding in Home → Merge Queries: it joins two tables on a matching column, like XLOOKUP applied to entire tables at once. If you have a rep-to-target lookup table, you can bolt it onto your sales data here instead of writing a lookup formula down 10,000 rows.

Unpivot — the one nobody knows and everybody needs

Our Week1–Week4 columns are a layout problem. Pivot tables, charts, and lookups all want a flat list: one row per rep, product, and week. Doing this with formulas is miserable. In Power Query:

1. Select the Rep, Region, and Product columns.
2. Right-click → Unpivot Other Columns.

That is the whole procedure. You now have columns Rep, Region, Product, Attribute, Value — the four week columns became two columns (“Week1”, 1200, etc.). Rename Attribute to Week. The beautiful part: “Unpivot Other Columns” means when February’s file shows up with a Week5 column, the step still works without editing.

Filter rows

Click the dropdown arrow on any column header — it works like an AutoFilter, but the filter becomes a permanent step. Common moves: filter out the 0 values, remove the “Total” rows that exports love to include, or use Home → Remove Rows → Remove Blank Rows instead of deleting blank rows by hand. Text columns get Text Filters → Does Not Contain for killing junk rows by keyword. There’s also Home → Remove Rows → Remove Duplicates, a self-maintaining version of Excel’s Remove Duplicates button, and the editor’s Transform → Format → Trim does what the TRIM function does — without a helper column.

Step 4: Load It, Then Never Do This Again

Click Home → Close & Load. The clean data lands on a new sheet as an Excel Table, connected to your query.

Next month, when the new file arrives (saved over the old one, same name and location):

• Data → Refresh All, or Ctrl+Alt+F5.

Every step replays: split, rename, unpivot, filter. Thirty minutes of Monday cleanup becomes one keystroke. If you build a pivot table on the query’s output table, refresh the query and the pivot together and the whole report updates end to end. (And if you ever hand the file to someone who shouldn’t refresh it, you can remove the data connections and keep just the values.)

The Payoff Move: Append a Whole Folder of Files

Here is where skeptics convert. Suppose those monthly CSVs accumulate — Sales_January.csv, Sales_February.csv, and so on, all with the same column layout, all in one folder.

1. Data → Get Data → From File → From Folder, and point at the folder.
2. Click Combine & Transform Data.
3. Power Query stacks every file into one table and adds a Source.Name column so you know which file each row came from.
4. Apply your same transforms (split, unpivot, filter) on the combined result.

Drop next month’s file into the folder and hit Refresh — it is absorbed automatically. No opening files, no copy-paste, no manually combining workbooks. Twelve files or twelve hundred, the recipe is identical. Two rules keep it bulletproof: keep column headers consistent across files, and keep stray files (notes, old versions) out of the folder — or add a filter step on Source.Name to ignore them.

FAQ

Do I need to learn M code to use Power Query?

No. Everything in this article is point-and-click; the M code is generated behind the scenes. The only time most users peek at it is the formula bar in the editor, and even that is optional. Years of productive use require zero M.

Will my coworkers need Power Query installed to open my file?

No. The query’s output is a normal Excel Table — anyone can open, read, and pivot it. They only need Power Query (built into Excel 2016 and later, and Excel for Microsoft 365) if they want to refresh or edit the query itself.

Why is my Refresh failing with a “file not found” error?

The query stores the full file path from when you built it. If the source file was renamed, moved, or lives on a drive letter that changed, the path breaks. Fix it in Data → Queries & Connections, double-click the query, click the gear icon next to the Source step, and browse to the new location.

Power Query or pivot table — which one do I need?

Both, usually, in sequence. Power Query cleans and shapes raw data (rows in, rows out); a pivot table summarizes it (rows in, totals out). The strongest pattern in Excel reporting is a query that feeds a pivot: refresh once, and the whole chain updates.

Does Power Query work on Mac?

Yes, with caveats. Excel for Microsoft 365 on Mac supports refreshing queries and creating them from local files, including From Folder. Some sources (certain databases, web connectors) and some editor features still lag the Windows version, so heavy query authoring is smoother on Windows.