Power QueryExercises

Choose Columns vs Remove Columns

Pick the column step that matches your intent so the query stays correct when the source schema changes.

~9 min read · beginner · Module 4: Working with columns

By Tahir Nas · Examples run in our simulator · Published · Updated

No prerequisites: you can start here.

Most exports carry more columns than a report needs. Power Query gives you two ways to get rid of them, and they look identical in the preview today. They behave very differently the day the source system adds a column.

Two steps, two intents

  • Choose Columns (Table.SelectColumns) lists the columns to keep. Anything not on the list disappears, including columns that appear in the future.
  • Remove Columns (Table.RemoveColumns) lists the columns to drop. Everything else stays, including columns that appear in the future.

Worked example

A dispatch sheet needs only OrderID, Customer and Qty from the orders export. Next month the export gains a SalesChannel column. Run both versions against next month's data:

let
    NextMonth = #table({"OrderID", "Customer", "Qty", "UnitPrice", "SalesChannel"}, {{"A-201", "Ana Ruiz", 3, 12.5, "Web"}}),
    Chosen = Table.SelectColumns(NextMonth, {"OrderID", "Customer", "Qty"})
in
    Chosen

Result

OrderIDCustomerQty
A-201Ana Ruiz3

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Choosing still returns exactly three columns. The editor names this step Removed Other Columns, but the function is Table.SelectColumns.
let
    NextMonth = #table({"OrderID", "Customer", "Qty", "UnitPrice", "SalesChannel"}, {{"A-201", "Ana Ruiz", 3, 12.5, "Web"}}),
    Removed = Table.RemoveColumns(NextMonth, {"UnitPrice"})
in
    Removed

Result

OrderIDCustomerQtySalesChannel
A-201Ana Ruiz3Web

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Removing lets the new SalesChannel column through.

Neither is always right. Ask what should happen to a column nobody has seen yet. For a report with a fixed layout, new columns are noise, so choose. For a data feed where you only need to hide a couple of sensitive fields, new columns are probably wanted, so remove. And if a new column could contain personal data, choosing is the safer default: nothing reaches the output unless you list it.

Checklist

  • Decide what a brand-new column should do before picking the step.
  • Choose for fixed report layouts and for anything that might carry personal data.
  • Remove for 'everything except' feeds.
  • Keep the default error on missing columns unless you have a reason not to.

Where to find this in Excel

Excel for Windows
Home > Choose Columns, or Home > Remove Columns > Remove Other Columns
What you see
Choose Columns opens a checklist of every column; Remove Other Columns keeps only the columns you selected.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Choose or remove columns (Microsoft Learn).

Common mistakes

  • Using Remove Columns for a fixed-layout report, so a new source column appears in the output after refresh.
  • Using Choose Columns for a feed that should pass new columns through, so new data is silently dropped.
  • Thinking the step called Removed Other Columns removes a fixed list; it keeps one.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: Home → Choose Columns, or select columns and right-click → Remove Other Columns (both write Table.SelectColumns). Remove Columns writes Table.RemoveColumns.
  • In Power BI, removing columns before loading also makes the model smaller and refresh faster.
  • The simulator implements both functions and their optional MissingField argument.

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's free, with no account needed.

Practise: Keep only the columns a report needs (kite shop)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.