Where Power Query Lives in Excel (and How to Open It)
Power Query is not a separate program. It is a built-in engine that appears under Data > Get & Transform Data on the ribbon, and it has its own window called the Power Query Editor. On Windows, look for the tab that says Data, then the Get & Transform Data group. That group has buttons named Get Data, Refresh All, and Queries & Connections. Every one of those routes into the same editor. On Excel for Mac, the ribbon path differs a little, but the same Get Data menu exists under the Data tab, and the underlying operations are identical.
The mental model I keep for people is simple: Power Query is where you shape data, and the sheet is where you present it. You should never paste raw exports into a sheet, delete columns, and hope the formatting survives. Instead, load the raw file into Power Query, clean it there, and send only the tidy result back to Excel. That split is what makes the whole thing repeatable, because the cleanup lives inside a query, not in a pile of manual edits. If your cleanup needs to happen before anything reaches a sheet, Power Query is the right tool, but for the manual fixes you still make directly in cells, see this guide to cleaning data in Excel.
Click a cell inside your data, then go to Data > From Table/Range. Excel wraps your range into a table object and opens the Power Query Editor automatically. This is the fastest entry point when you already have data sitting in a sheet. The button may be labeled From Table/Range on Windows and From Sheet on Mac, but both do the same thing.
Go to Data > Get Data > From File > From Excel Workbook, then pick your .xlsx file. Power Query opens a navigator window showing every sheet and named table in the file. You can select one, several, or all of them, and preview the first rows on the right before you load. This matters because you can inspect column headers and spot formatting problems before you commit.
In the navigator, the Load button dumps the data straight into a new sheet. The Transform Data button opens the editor first so you can shape it. For anything you plan to reuse, choose Transform Data and build the cleanup now. Loading raw data directly to a sheet is how people end up cleaning the same export over and over.
Power Query is free and included with Excel 2016, Excel 2019, Excel 2021, and Microsoft 365 on Windows. Mac support arrived in Excel 365 for Mac in 2019. If you are on Excel 2013, you can install the free Power Query add-in from Microsoft, but the modern versions have it built in. If you are about to tell a client to 'just use Power Query', check their version first, because a lot of corporate installs lag behind.
Merge Two Tables on a Key Column (Sales + Product)
Merging is Power Query's version of a join, and it is the operation you will use when two tables share a key. In my experience the most common real case is a sales fact table and a product lookup table. Your Sales table has columns ProductID, Date, Units, and Revenue, and your Product table has ProductID, ProductName, Category, and UnitCost. You want the product name and category on every sales row so you can pivot and analyze by product later. That is a merge on ProductID.
Before you merge, check for a mismatch trap: the Sales table might hold ProductID values like 101, 102, and 103, while the Product table holds them as text with a leading zero like 00101. A merge on those columns returns rows with no match and quietly fills Null. I usually add a step that converts both key columns to the same type before merging, because this single mismatch causes more empty merges than anything else I see.
Select the ProductID column in each query, go to Home > Data Type, and choose the same type in both, typically Whole Number or Text. Check the data type badge under the column header, which shows a number icon for Whole Number or ABC for Text. When both badges match, a merge on that column will find the matches you expect.
In the Sales query, go to Home > Merge Queries > Merge Queries as New. In the dialog, pick the Product query as the second table, click the ProductID column in each table so they highlight, and leave the default join kind set to Left Outer. Left Outer keeps every row from Sales and only brings in Product columns where a match exists, which is what you want when Sales is your fact table.
Power Query adds a new column named Product with a table icon in each cell. Click the expand arrows at the top of that column, uncheck ProductID (you already have it), and check ProductName and Category. Leave the 'Use original column name as prefix' box unchecked unless you want names like Product.ProductName. After you click OK, the ProductName and Category columns appear on every sales row.
After expanding, look at the ProductName column for Nulls. Filter that column to show only non-null values, or inspect the unmatched rows with a filter on 'does not equal null'. In my experience these are usually typo'd ProductIDs in the source system. Flag them and fix them at the source, because merging in Power Query cannot repair data that was bad when it arrived.
I recommend using Merge Queries as New instead of Merge Queries when you are still figuring out the join, because it leaves the original query untouched. If you expand the wrong columns, you undo in the new query and lose nothing. Once the merge is proven, you can promote it into the main query or delete the scratch copy. A separate scratch query costs nothing and saves you from rebuilding a loaded table.
Append Several Sheets (or Files) Into One Long Table
Appending is stacking, and it is what you want when the same data lives in many places: a workbook with a sheet per month, or a folder with one file per region. Instead of copy-pasting twelve sheets into one sheet and praying the headers line up, you let Power Query stack them by column name. The rule is simple: append matches columns by name, not by position. So Sheet1 with columns Date, Region, Sales and Sheet2 with columns Region, Date, Sales will stack correctly, because the names match.
You will hit this specific problem: monthly exports where one sheet is missing a column or has an extra one. When that happens, append does not fail — it creates Null for the missing column on those rows and adds the extra column with Null for everyone else. That is usually acceptable, but you should know it happens silently. I find it worth adding a small column to record which source file each row came from, so a missing region can be traced back to its export.
From the workbook, use Get Data > From Excel Workbook and select every sheet you want to stack in the navigator, or load one sheet and duplicate it. Clean each sheet enough that the headers match before you append. The cleanest approach is to load the whole workbook, select all sheets, and let Power Query create one query per sheet.
In a blank query, go to Home > Append Queries > Append Queries as New. In the dialog, choose Three or more tables, add all your sheet queries to the right-hand list, and click OK. Power Query stacks them into one query ordered by the list order you set. The output contains every row from every source.
If a sheet starts with a bold title row like 'March Sales Report' and the real headers sit in row 2, Power Query treats row 1 as the header. Fix it by going to Home > Use First Row as Headers, or on the transformed result use Transform > Use First Row as Headers. Then rename the columns so they match the other sheets. Mismatched headers are the number one append mistake I debug for people.
Before appending, add a custom column in each source query with the sheet name, using Add Column > Custom Column with a formula like ="Jan" or ="Region-East". I usually name the column Source. After the append, every row shows where it came from, which makes a later filter like 'only show March' trivial and keeps audit trails clean.
Unpivot a Wide Table Into a Long Table (and Back)
Unpivot is the operation that surprises most people, because it does the opposite of what a pivot table does. Pivot turns long data into a summary matrix; unpivot turns a matrix back into long data. The reason you need it: analysts and tools like pivot tables and most charting expect one row per observation. A wide table that lists Product in the first column and Q1, Q2, Q3, Q4 across the top is comfortable for a human to read but wrong for analysis. Unpivoting turns Product | Q1 | Q2 | Q3 | Q4 into Product | Quarter | Sales, with four rows per product.
You will hit this almost weekly if you receive reports formatted for executives, who like wide tables. The rule I teach: if a column header is a value that belongs in a row (like a quarter or a month), that table is wide and needs unpivoting. Every Excel export I get from marketing tools arrives wide, and unpivot is the first step I run on almost all of them. Once the data is long, it plugs straight into a pivot table for summarization without you reshaping anything by hand.
In the wide table, the columns you do NOT unpivot are your identifiers. In Product | Q1 | Q2 | Q3 | Q4, the identifier column is Product. Select Product, right-click it, and choose Unpivot Other Columns. That tells Power Query to keep Product intact and turn every remaining column (Q1 through Q4) into two new columns named Attribute and Value.
Power Query names the two new columns Attribute and Value, which are meaningless to anyone reading the output. Rename Attribute to Quarter and Value to Sales by double-clicking the header. After renaming, each product row becomes four rows: one per quarter, with the quarter name in the Quarter column and the number in Sales. That is the long format your pivot tables and charts want.
The Value column may arrive as text, especially if the wide table had any comma formatting. Select the Sales column, go to Home > Data Type, and choose Decimal Number or Whole Number. If any cell cannot convert, Power Query flags an error you can inspect. Fixing the type here beats discovering strings downstream when your SUM returns zero.
Sort by Product and Quarter if order matters, then go to Home > Close & Load to send the long table back to Excel. From there, you can build a pivot table or a line chart by quarter directly, because the data is now in the shape those tools expect. You have just replaced a manual 'copy this column over here' routine with a step you can replay on next month's export.
If you only want to unpivot a few columns instead of keeping a few, select the columns to unpivot and choose Unpivot Columns instead of Unpivot Other Columns. I usually use Unpivot Other Columns because wide exports have one or two identifiers and ten value columns, so it is less clicking. But when a table has a mix where only three of fifteen columns are values, Unpivot Columns is the faster pick.
Split Columns, Remove Duplicates, and Trim Spaces
The day-to-day cleaning in Power Query is split, dedupe, and trim, and each one replaces a slow manual habit. Splitting takes one column with packed data and divides it by a delimiter. Removing duplicates drops repeated rows. Trimming strips the trailing spaces that break lookups and joins. Done in the editor, all three become recorded steps you can re-run instead of one-off fixes.
The concrete case I see constantly: a Full Name column holding 'Jane Smith' that needs to become First Name and Last Name, and an Address column holding '123 Main St, Boston, MA' that needs splitting into Street and City and State. Each is a Split Column by Delimiter. Meanwhile a Contacts table that has the same person listed three times needs a Remove Duplicates pass keyed on the email column. Cleaning the data up front also makes it behave reliably when you build validated drop-down lists on top of it later.
Select the Full Name column, go to Home > Split Column > By Delimiter, choose Space as the delimiter, and click OK. Power Query produces First Name and Last Name. For the Address column, split by Comma instead, which yields Street, City, and State in separate columns. You can split at every occurrence or only the first; for names and addresses, 'split at the leftmost' is usually right.
Select the email column in your Contacts table, then go to Home > Remove Rows > Remove Duplicates. Power Query keeps the first occurrence and drops the rest, and it does it instantly even on hundreds of thousands of rows. Select the column first — removing duplicates on the whole table when you only wanted email-based dedupe will drop rows that differ in other columns.
Select the columns with names or codes, then go to Transform > Format > Trim. This strips leading and trailing spaces that you cannot see but that break merges and lookups. After trim, use Transform > Format > Clean, which removes non-printing characters like line breaks inside cells. In my experience a trim-then-clean pass fixes most 'why won't this match' mysteries on imported data.
When a column has errors, select it and go to Transform > Replace Values > Replace Errors, and type a default like 0 or 'Not provided'. For blanks, use Replace Values with the blank cell value. This keeps your final table free of Nulls and errors that would otherwise break a downstream SUMIFS or a merge. Decide the default deliberately, not by clicking through.
The M Language vs Point-and-Click: What You Actually Need
Every click you make in the Power Query Editor records an M step in a hidden formula bar, and M is the scripting language underneath. You do not need to write M from scratch to be effective, but you need to read it, because that is how you debug and how you learn the advanced transforms. When you click something, look at the formula bar and read what was recorded. Over a few weeks, the pattern for filtering, renaming, and merging becomes recognizable.
The common functions you will meet: Table.SelectRows for filtering, Table.RemoveColumns for dropping columns, Table.TransformColumnTypes for setting data types, and Table.UnpivotOtherColumns for the unpivot you did above. You will also see Table.Merge and Table.Combine for merge and append. I recommend not memorizing these — instead, click the button, read what M it generated, and let the names sink in naturally.
In a query, right-click a column, choose Filter, and pick a value. The formula bar now shows a Table.SelectRows step with a condition inside. For example, = Table.SelectRows(Previous, each [Region] = "West"). Read it as 'take the prior table, keep rows where Region equals West'. That one pattern covers most filtering you will ever do.
After a merge, the formula bar shows a Table.NestedJoin step that names both tables and the join key. Recognizing this step helps you verify you joined on the right column and the right kind. If you ever see a merge that references a column you did not intend, the M line makes it obvious, and you can delete that step in the Applied Steps pane on the right.
In the Applied Steps pane on the right side of the editor, right-click a step like 'Filtered Rows' and choose Rename, then call it 'Keep West Region Only'. Descriptive step names make a query readable when you reopen it six months later, and they cost you nothing. I name the important steps in every query I build because future-me cannot remember why a step exists.
Select a step in Applied Steps, then click the formula bar and type a small edit, like changing "West" to "East" in a filter. Press Enter to apply. This is the fastest way to tweak a single value without rebuilding the step. Editing M directly is where beginners get stuck, so keep edits tiny at first — changing one quoted value is safe, rewriting a whole function is not.
In my experience, the fastest way to learn M is to record a click, read the generated line, and then change one thing in it. Do not open the M reference docs cold. Build a query with the UI, then edit a single literal in the formula bar and watch what happens. Ten minutes of that beats an hour of reading documentation, and it builds the habit of reading M, which is the skill that separates people who reuse queries from people who rebuild them.
A Real Workflow: Weekly Sales Report in Four Steps
Here is the workflow I run every week on a real dataset, so you can see how these operations chain together. Every Monday I receive an export from the sales team named sales-export.xlsx with one sheet per region: East, West, and Central. Each sheet is wide, with Product as the first column and Q1 through Q4 as the remaining columns, plus a few rows of duplicates from a glitchy export. My job is one clean long table I can pivot and chart.
The full chain in order: load the workbook, append the three sheets, unpivot the wide columns, remove the duplicates, then Close & Load. Each operation feeds the next, and the whole thing reruns on next week's export with one click of Refresh All. This is the difference between spending Monday morning on cleanup and spending it on analysis.
Get Data > From Excel Workbook, select sales-export.xlsx, and load East, West, and Central as separate queries. In a blank query, Append Queries as New and stack all three into one query. Because all three sheets share the same headers (Product, Q1, Q2, Q3, Q4), the append lines them up cleanly with no Nulls to clean.
Select the Product column and choose Unpivot Other Columns. Rename Attribute to Quarter and Value to Sales, then change Sales to Whole Number. The wide table with four quarter columns is now a long table with one Sales value per product per quarter. From 300 rows, you now have roughly 1200, which is exactly what a pivot table wants.
Select the Product column and run Home > Remove Rows > Remove Duplicates. The glitchy export produced a few repeated rows for the same product and quarter; deduping on Product removes the duplicates that have identical product names. For a stricter dedupe that removes full duplicate rows, select all columns before running Remove Duplicates.
Add a custom column named ReportDate with a formula like =DateTime.Date(DateTime.LocalNow()) so every row records which week's export it came from. Then go to Home > Close & Load. You now have a long, clean table with Product, Quarter, Sales, and ReportDate. Next Monday, drop the new file over the old one and press Refresh All — the entire pipeline replays.
Practice: Rebuild These Transforms on a Copy
Reading about Power Query sticks better when you do it. Build one small workbook with two sheets, then run the operations in order. The point is to feel where the buttons live and to see the recorded M steps, because that is what makes the skill transfer to your real files. You do not need a huge dataset — five rows are enough to see each transform do its job.
On Sheet1, make a table with columns Product, Q1, Q2, Q3, Q4 and three rows of fake products. Load it via Data > From Table/Range, unpivot the quarter columns, and rename Attribute to Quarter and Value to Sales. Confirm each product now has four rows. Then pivot it back with a pivot table and watch it return to the wide shape — that is the inverse relationship made concrete.
On Sheet2 and Sheet3, create a small Sales table with ProductID, Units, Revenue and a Product table with ProductID, ProductName, Category. Merge them on ProductID as shown above and expand ProductName and Category. Test the mismatch trap by entering one ProductID as text with a leading zero and watch that row come back Null — then fix the type and re-run.
After you are happy with a query, save the workbook as .xlsx with the connection stored. To verify it refreshes, change a value in the source data, then go to Data > Refresh All. The output should update automatically. If it does not, check the Applied Steps to confirm the query still points at the right table. This refresh test is how you know the pipeline is real and not just a one-off script.
When you are learning, keep the query names short and the steps renamed. A query called SalesClean with steps named AppendSheets, Unpivot, Dedupe, and Load is much easier to debug than one called Query1 with fifteen steps named 'Filtered Rows'. Good names are the cheapest maintenance habit in Power Query, and they are what let you hand a file to a colleague without a walkthrough.
