Wicked Smart Data
LearnInsightsAboutContact
Sign InLet's Build
LearnInsightsAboutContact
Sign InLet's Build
Wicked Smart Data

Intelligence, automation, and expert execution — plus an elite library of free knowledge. We turn complexity into competitive advantage.

Start a conversation

Platform

  • Learning Paths
  • Insights
  • RSS Feed

Company

  • About
  • Contact
  • Work With Us

Legal

  • Privacy Policy
  • Terms of Service

© 2026 Wicked Smart Data. All rights reserved.

Intelligence · Automation · Advantage

All Insights
Power Query

Renaming, Reordering, and Removing Columns in Power Query: Structuring Your Data for Downstream Use

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.

🌱 Foundation17 min readAug 23, 2026Updated Sep 23, 2026
Renaming, Reordering, and Removing Columns in Power Query: Structuring Your Data for Downstream Use
On this page
  • Introduction
  • Prerequisites
  • Why Column Structure Matters More Than You Think
  • Setting Up: Our Example Dataset
  • Step 1: Removing Columns You Don't Need
  • The Two Philosophies of Column Removal
  • How to Remove Other Columns
  • Step 2: Renaming Columns
  • Renaming One Column at a Time
  • A Cleaner Approach: Rename All at Once
  • Naming Conventions Worth Following
  • Step 3: Reordering Columns
  • Dragging Columns in the Preview
  • Using the Move Menu
  • The M Code Behind Reordering
  • When Reordering Really Matters
  • How These Steps Work Together in the Query
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Summary & Next Steps

Renaming, Reordering, and Removing Columns in Power Query: Structuring Your Data for Downstream Use

Introduction

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:

  • How to rename individual columns and bulk-rename many columns efficiently
  • How to reorder columns by dragging, by menu, and programmatically
  • How to remove columns — and the crucial difference between removing specific columns versus removing "other" columns
  • How the M code behind these operations actually works
  • How to structure your column cleanup steps in a logical, maintainable order

Prerequisites

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.


Why Column Structure Matters More Than You Think

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.


Setting Up: Our Example Dataset

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 identifier
  • acct_nm — the account (customer) name
  • rep_id_fk — the sales rep's foreign key ID
  • rep_nm — the sales rep's name
  • crt_dt — the date the record was created
  • upd_dt — the last updated date
  • rgn_cd — a region code
  • prod_cat — product category
  • prod_nm — product name
  • qty — quantity sold
  • amt_usd_gross — gross revenue in USD
  • dscnt_pct — discount percentage
  • amt_usd_net — net revenue in USD
  • is_rtn — whether it was a return (1 or 0)
  • src_sys — the source system name (always "CRM-Legacy")
  • btch_id — the batch processing ID
  • etl_ts — the ETL (data pipeline) timestamp

For 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.


Step 1: Removing Columns You Don't Need

The first thing to do is clear out the clutter. It's easier to rename and reorder seven columns than seventeen.

The Two Philosophies of Column Removal

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?

  • If you used Remove Columns listing specific names to delete, the new territory_cd column passes through unchanged. It just appears in your output, which might or might not be what you want.
  • If you used Remove Other Columns, the new column gets silently dropped because it wasn't in your "keep" list.

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.

How to Remove Other Columns

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.


Step 2: Renaming Columns

Now that we have just our seven columns, let's make them readable.

Renaming One Column at a Time

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 → Category
  • prod_nm → Product
  • qty → Quantity
  • amt_usd_net → Net Revenue

As you do each rename, Power Query adds a new step. After seven renames, your Applied Steps pane has seven separate "Renamed Columns" steps.

A Cleaner Approach: Rename All at Once

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.

Naming Conventions Worth Following

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.


Step 3: Reordering Columns

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.

Dragging Columns in the Preview

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.

Using the Move Menu

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.

The M Code Behind Reordering

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.ReorderColumns step, 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.

When Reordering Really Matters

Column order matters most in two situations:

  1. When humans are reading the output. A well-ordered table is easier to scan, audit, and validate.
  2. When a downstream process depends on column position. Some systems (older CSV imports, certain APIs) read data by column position rather than column name. If you're feeding data into one of those systems, column order is critical.

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.


How These Steps Work Together in the Query

Let's zoom out and look at the full sequence of Applied Steps we've built:

  1. Source — connects to the file
  2. Promoted Headers — (auto-generated) treats the first row as column names
  3. Changed Type — (auto-generated) guesses data types
  4. Removed Other Columns — keeps only the seven columns we need
  5. Renamed Columns — renames all seven in one step
  6. Reordered Columns — puts them in the right sequence

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.


Hands-On Exercise

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:

  1. 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).

  2. Use Remove Other Columns to keep only: f_nm, l_nm, dept_cd, hire_dt, base_sal, bonus_amt, loc_cd.

  3. Edit the resulting Table.SelectColumns step to verify you can read the M code and understand what it's doing.

  4. Add a single Renamed Columns step (using the formula bar to write all renames at once) with these mappings:

    • f_nm → First Name
    • l_nm → Last Name
    • dept_cd → Department
    • hire_dt → Hire Date
    • base_sal → Base Salary
    • bonus_amt → Bonus
    • loc_cd → Location
  5. Add a Reordered Columns step to put them in order: First Name, Last Name, Department, Hire Date, Base Salary, Bonus, Location.

  6. 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.)


Common Mistakes & Troubleshooting

"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.


Summary & Next Steps

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:

  • Filtering Rows in Power Query — now that your columns are structured correctly, learn to control which rows make it into your output
  • Changing Data Types — understand why Power Query's auto-detected types are often wrong and how to fix them correctly
  • Merging Queries — once you're comfortable with single-table cleanup, learn how to combine data from multiple sources using Power Query's join operations

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.

Work With Us

From insight to implementation

Reading is the start. When you're ready to build the data, automation, or AI systems behind it, our team turns strategy into shipped results.

Let's Build

Power Query Essentials

Previous

Building Multi-Stage Staging Architectures in Power Query: Separating Raw, Cleansed, and Conformed Layers for Scalable ETL Pipelines

Next

Implementing Slowly Changing Lookup Tables in Power Query: Point-in-Time Merges with Effective Date Ranges

Related Insights

Power QueryFoundation

Understanding Value Types and Primitive Operations in M: Numbers, Text, Logical, and Null Foundations

16 min
Power QueryFoundation

Promoting Queries to the Data Model vs. Connection Only in Power Query: Controlling What Loads and Why

16 min
Power QueryExpert

Implementing Custom Connection Retry and Backoff Logic in Power Query M: Building Fault-Tolerant Data Source Connectors for Unreliable Networks and Throttled APIs

28 min

On this page

  • Introduction
  • Prerequisites
  • Why Column Structure Matters More Than You Think
  • Setting Up: Our Example Dataset
  • Step 1: Removing Columns You Don't Need
  • The Two Philosophies of Column Removal
  • How to Remove Other Columns
  • Step 2: Renaming Columns
  • Renaming One Column at a Time
  • A Cleaner Approach: Rename All at Once
  • Naming Conventions Worth Following
  • Step 3: Reordering Columns
  • Dragging Columns in the Preview
  • Using the Move Menu
  • The M Code Behind Reordering
  • When Reordering Really Matters
  • How These Steps Work Together in the Query
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Summary & Next Steps