Raw data exports are rarely structured the way you need them. Learn how to rename cryptic column names, drop unnecessary fields, and arrange columns in a logical order using Power Query — and understand the M code doing the work behind the scenes.
Imagine you've just connected Power Query to a database export from your company's CRM system. The data arrives with column names like acct_nm, crt_dt, rep_id_fk, and amt_usd_gross — cryptic abbreviations that made sense to whoever designed the database schema years ago but mean nothing to the sales analysts who need to use this report. On top of that, the columns are in no particular order, and there are seventeen of them when you only need six. Before you can build a single calculation or load this data into a report, you need to do some housekeeping.
This kind of cleanup work — renaming columns to human-readable names, reordering them into a logical sequence, and removing the ones you don't need — is one of the most common and important tasks you'll perform in Power Query. It sounds mundane, but doing it well is the difference between a data model that downstream users (including future you) can navigate confidently and one that causes confusion and errors. More importantly, Power Query lets you do all of this in a way that's repeatable and automatic — once you set it up, every time new data flows through, it gets cleaned up the same way.
By the end of this lesson, you'll be able to confidently reshape the column structure of any dataset in Power Query. You'll understand not just the mechanical steps, but why each operation matters and how to avoid the mistakes that trap beginners.
What you'll learn:
You should have Power Query open and know how to connect to at least one data source (a CSV file or Excel table works perfectly). You should understand what a "step" is in the Applied Steps pane — if you've loaded data and seen the list of steps on the right side of the Power Query Editor, you're ready. No formula or M language experience is required.
Before we get into the mechanics, let's establish why this work is worth doing carefully.
When your data lands in Excel, Power BI, or any other tool downstream, the column names become the labels that users see, the field names that formulas reference, and the identifiers that reports depend on. If a column is named rep_id_fk in your query, that's what shows up in every pivot table field list, every DAX formula, every chart axis label. Cleaning this up at the Power Query stage — before the data goes anywhere — means you fix it once, and it's fixed everywhere.
Reordering matters for similar reasons. Humans read tables left to right and expect the most important or identifying information to appear first. A sales report that starts with twelve internal flags before showing the customer name and revenue figure creates unnecessary friction. Column order also affects how quickly you can visually validate data when you're building and debugging queries.
Removing unnecessary columns is about more than tidiness. Every column you carry through your query is data that gets loaded into memory and refreshed on every update. Stripping out columns you don't need makes your queries faster, your data models leaner, and your outputs easier to understand.
Throughout this lesson, we'll work with a fictional sales export. Picture a CSV file called sales_export.csv that arrives from a legacy system with these columns, in this order:
rec_id — an internal record identifieracct_nm — the account (customer) namerep_id_fk — the sales rep's foreign key IDrep_nm — the sales rep's namecrt_dt — the date the record was createdupd_dt — the last updated datergn_cd — a region codeprod_cat — product categoryprod_nm — product nameqty — quantity soldamt_usd_gross — gross revenue in USDdscnt_pct — discount percentageamt_usd_net — net revenue in USDis_rtn — whether it was a return (1 or 0)src_sys — the source system name (always "CRM-Legacy")btch_id — the batch processing IDetl_ts — the ETL (data pipeline) timestampFor our sales report, we only need: account name, rep name, region, product category, product name, quantity, and net revenue. We want them in that logical order. And we want the names to be readable.
To follow along, load this file into Power Query: open Excel, go to the Data tab, click Get Data → From File → From Text/CSV, select your file, and then click Transform Data to open the Power Query Editor.
The first thing to do is clear out the clutter. It's easier to rename and reorder seven columns than seventeen.
Power Query gives you two fundamentally different approaches to removing columns, and understanding the difference will save you from headaches later.
Remove Columns — You explicitly tell Power Query which columns to delete. The M code keeps a list of column names to remove.
Remove Other Columns — You select the columns you want to keep, and Power Query deletes everything else. The M code keeps a list of columns to retain.
Here's why this distinction matters: what happens when next month's data export arrives with a new column, say territory_cd?
territory_cd column passes through unchanged. It just appears in your output, which might or might not be what you want.Neither behavior is wrong — they're appropriate in different situations. If you're building a query where you want to capture everything except a few known junk columns, use Remove Columns. If you're building a query where you've carefully selected exactly the fields you need and don't want surprise columns sneaking in, use Remove Other Columns.
For our sales report, we know exactly what we need, so Remove Other Columns is the right call.
In the Power Query Editor, hold Ctrl and click the headers of the columns you want to keep: acct_nm, rep_nm, rgn_cd, prod_cat, prod_nm, qty, and amt_usd_net.
With all seven selected, right-click on any of the selected column headers. In the context menu, choose Remove Other Columns.
Watch the Applied Steps pane on the right — a new step called "Removed Other Columns" appears. Your dataset now shows only seven columns. The M code Power Query generated looks like this:
= Table.SelectColumns(
#"Previous Step Name",
{"acct_nm", "rep_nm", "rgn_cd", "prod_cat", "prod_nm", "qty", "amt_usd_net"}
)
Table.SelectColumns is the M function behind "Remove Other Columns." It takes a table and a list of column names to keep. Notice that the list contains the original column names with their cryptic abbreviations — we haven't renamed anything yet. That's intentional. We remove first, then rename.
Tip: Always remove columns before renaming them. If you rename first and then remove, the M code for your removal step will contain the new names. If you then rename again or change your mind, those steps can fall out of sync. Working in remove-then-rename order keeps your steps logical and easier to maintain.
If you wanted to use the regular Remove Columns approach instead (to delete specific columns), you'd select the columns you want to delete, right-click, and choose Remove Columns. The M function behind that operation is Table.RemoveColumns, and it takes a list of columns to drop.
Now that we have just our seven columns, let's make them readable.
The simplest way to rename a column is to double-click its header. The header becomes an editable text field. Type the new name and press Enter.
Let's rename acct_nm to Customer. Double-click the acct_nm header, type Customer, and press Enter.
A new step called "Renamed Columns" appears in Applied Steps. The M code looks like:
= Table.RenameColumns(
#"Removed Other Columns",
{{"acct_nm", "Customer"}}
)
Table.RenameColumns takes a list of pairs — each pair is {old name, new name}. Right now it has one pair. Let's keep renaming.
Double-click rep_nm, change it to Sales Rep. Double-click rgn_cd, change it to Region. Continue:
prod_cat → Categoryprod_nm → Productqty → Quantityamt_usd_net → Net RevenueAs you do each rename, Power Query adds a new step. After seven renames, your Applied Steps pane has seven separate "Renamed Columns" steps.
Seven separate rename steps is inefficient. Each step is another layer of transformation Power Query has to process, and it makes your Applied Steps pane cluttered and hard to read. The better approach is to put all your renames into a single step.
Here's how: right-click on the last "Renamed Columns" step in your Applied Steps pane and click Delete Until End — wait, don't do that yet. Instead, let's use a smarter workflow.
Click on the first "Renamed Columns" step in your Applied Steps pane to select it. Then click the fx button in the formula bar (or directly edit the formula bar) to see the M code. You can manually add all the rename pairs to the single Table.RenameColumns call:
= Table.RenameColumns(
#"Removed Other Columns",
{
{"acct_nm", "Customer"},
{"rep_nm", "Sales Rep"},
{"rgn_cd", "Region"},
{"prod_cat", "Category"},
{"prod_nm", "Product"},
{"qty", "Quantity"},
{"amt_usd_net", "Net Revenue"}
}
)
After entering this, delete the subsequent six individual rename steps by right-clicking each one and choosing Delete. (Start from the bottom to avoid reference errors.)
Warning: Be careful with column names that contain spaces. In M code, when you reference a column name with a space in a later step (not in the rename step itself), you need to wrap it in double quotes — which you're already doing in list format. But if you're writing custom M expressions that reference columns directly,
[Net Revenue](with no quotes in record field syntax) works fine in M. Just know that spaces in column names are perfectly valid in Power Query even though some other tools dislike them.
Choosing good column names is a small design decision with lasting consequences. Here are some practical guidelines:
Be descriptive but not verbose. "Customer" is better than "acct_nm" but also better than "Customer Account Name in System." One to three words is usually right.
Use title case for display-facing outputs. If this data is going into a report that end users see, "Net Revenue" looks more professional than "net_revenue" or "NETREVENUE."
Use snake_case for analytical outputs. If this data is feeding another system, a database, or Power BI where DAX will reference these columns, underscored lowercase names (net_revenue) are often easier to work with programmatically.
Be consistent. Pick one style and stick to it across all columns in the table and ideally across all tables in your model.
With our seven columns renamed, they're still in the order they arrived: Customer, Sales Rep, Region, Category, Product, Quantity, Net Revenue. Actually, that's a pretty good order already — but let's say the business wants Quantity and Net Revenue before Category and Product, because the financial figures are most important. We want: Customer, Sales Rep, Region, Quantity, Net Revenue, Category, Product.
The most intuitive method is simply dragging. Click on the "Quantity" column header and hold the mouse button down. Drag it to the left, past Region, and release it when you see the insertion indicator appear between Sales Rep and Region. Do the same for Net Revenue.
Each drag creates a new "Reordered Columns" step in Applied Steps. Like with renaming, multiple small steps accumulate. But the principle is sound for quick adjustments.
An alternative to dragging: right-click any column header and look at the Move submenu. You'll see options: Move to Beginning, Move to End, Move Before, Move After. These are useful when you need to move a column across a large table and dragging would mean scrolling across dozens of columns.
Whether you drag or use the menu, Power Query generates a Table.ReorderColumns call:
= Table.ReorderColumns(
#"Renamed Columns",
{"Customer", "Sales Rep", "Region", "Quantity", "Net Revenue", "Category", "Product"}
)
Table.ReorderColumns takes a complete, exhaustive list of column names in the order you want them. Every column in the table must appear in the list, or you'll get an error.
Tip: If you're manually writing or editing a
Table.ReorderColumnsstep, make sure your list includes every column. A common mistake is to list only the columns you want to move, forgetting the others. Power Query will throw an error telling you a column is missing from the list.
Column order matters most in two situations:
For most modern analytical tools like Power BI, column order is cosmetic — the tool references columns by name. But cosmetic improvements reduce friction and errors for real people, so don't dismiss them.
Let's zoom out and look at the full sequence of Applied Steps we've built:
This is a clean, logical flow that any future maintainer (or future you) can read and understand. Each step has a single clear purpose. Contrast this with a messy sequence where renaming happens in the middle, some columns are removed before and some after, and reordering is scattered across three steps — that query is a maintenance nightmare.
Tip: Think of your Applied Steps as telling a story: "First I got the data. Then I kept only what I needed. Then I made the names human-readable. Then I arranged them logically." A query that reads like a clear story is a query that's easy to fix when something breaks.
Let's put all three skills together with a structured exercise.
Setup: Create an Excel table (or CSV) with these columns in this order:
emp_id, f_nm, l_nm, dept_cd, hire_dt, term_dt, base_sal, bonus_amt, mgr_id, loc_cd, is_actv, sys_flag
Your goal: Transform this into a clean employee report with only: First Name, Last Name, Department, Hire Date, Base Salary, Bonus, and Location — in that order.
Steps to complete:
Load the data into Power Query via Excel's Data tab → Get Data → From Table/Range (if using an Excel table) or From Text/CSV (if using a file).
Use Remove Other Columns to keep only: f_nm, l_nm, dept_cd, hire_dt, base_sal, bonus_amt, loc_cd.
Edit the resulting Table.SelectColumns step to verify you can read the M code and understand what it's doing.
Add a single Renamed Columns step (using the formula bar to write all renames at once) with these mappings:
f_nm → First Namel_nm → Last Namedept_cd → Departmenthire_dt → Hire Datebase_sal → Base Salarybonus_amt → Bonusloc_cd → LocationAdd a Reordered Columns step to put them in order: First Name, Last Name, Department, Hire Date, Base Salary, Bonus, Location.
Check your Applied Steps pane — you should have no more than five or six steps, cleanly structured.
Bonus challenge: What happens if you add a new column called review_score to your source data and refresh the query? Does it appear in the output? Why or why not? (Hint: think about which removal approach you used.)
"Expression.Error: The column 'X' of the table wasn't found."
This is the most common error in Power Query, and column operations are the usual cause. It means a step is looking for a column name that doesn't exist — either because the source data changed the column name, or because an earlier step already removed or renamed it.
Click through your Applied Steps one by one, starting from the top, until you find the step that first shows the error. The column referenced in that step's M code is the one that doesn't exist at that point in the query.
Renaming creates multiple steps instead of one.
If you rename columns one at a time by double-clicking headers, you'll accumulate many individual steps. This works but is messy. Consolidate them by editing the first rename step to include all your rename pairs in a single Table.RenameColumns call, then delete the subsequent individual rename steps.
Reorder step errors because a column name is missing from the list.
Table.ReorderColumns requires every column to be listed. If you manually edit the M code and accidentally leave out a column, you'll get an error. Double-check your list against the columns visible in the data preview.
The wrong columns are being removed.
If you accidentally used "Remove Columns" when you meant "Remove Other Columns" (or vice versa), just delete that step from Applied Steps and redo it. This is exactly why building queries in the Power Query Editor is safe — every step is reversible.
Column names with special characters cause unexpected behavior.
Avoid column names with characters like #, $, %, [, or ] — these have special meaning in M and can break formulas that reference those columns. Parentheses, commas, and quotes are also problematic. Stick to letters, numbers, spaces, and underscores in your column names.
You've now learned the three fundamental column structure operations in Power Query — and more importantly, why and when to use each one.
Removing columns cleans out the noise and makes everything downstream simpler. Use Remove Other Columns (Table.SelectColumns) when you know exactly what you need and want to guard against surprise new columns. Use Remove Columns (Table.RemoveColumns) when you're dropping a few known problem columns and want everything else to pass through.
Renaming columns is where you translate from machine-readable abbreviations to human-readable labels. Do all your renames in a single Table.RenameColumns step for a clean, maintainable query. Choose a naming convention and apply it consistently.
Reordering columns puts information in the logical sequence your readers expect. Table.ReorderColumns requires every column to be listed — a small constraint that enforces completeness and clarity.
The sequence matters: remove first, rename second, reorder third. This order keeps your M code clean and your steps easy to read and debug.
Where to go next:
Column structure is the foundation. Get it right here, and every downstream step — from calculations to visualizations to data model relationships — becomes cleaner and more reliable.