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
ChosenResult
| OrderID | Customer | Qty |
|---|---|---|
| A-201 | Ana Ruiz | 3 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
let
NextMonth = #table({"OrderID", "Customer", "Qty", "UnitPrice", "SalesChannel"}, {{"A-201", "Ana Ruiz", 3, 12.5, "Web"}}),
Removed = Table.RemoveColumns(NextMonth, {"UnitPrice"})
in
RemovedResult
| OrderID | Customer | Qty | SalesChannel |
|---|---|---|---|
| A-201 | Ana Ruiz | 3 | Web |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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.
- beginner~5 minFree
Keep only the columns a report needs (kite shop)
Choose OrderID, Customer and Qty so the report stays the same shape even when the export gains new columns.
- beginner~5 minPro
Keep only the columns a report needs (bakery)
Choose OrderID, Customer and Qty so the report stays the same shape even when the export gains new columns.
- beginner~5 minPro
Keep only the columns a report needs (bike rental)
Choose OrderID, Customer and Qty so the report stays the same shape even when the export gains new columns.
- beginner~5 minFree
Remove salary and email before sharing a roster (logistics)
Share a staff list with managers without salary or email columns.
- beginner~5 minPro
Remove salary and email before sharing a roster (software)
Share a staff list with managers without salary or email columns.
- beginner~5 minPro
Remove salary and email before sharing a roster (hospital)
Share a staff list with managers without salary or email columns.
- beginner~5 minFree
Duplicate a column before changing it (logistics)
IT wants a lower-case Login built from Email, while HR needs the Email column left exactly as typed.
- beginner~5 minPro
Duplicate a column before changing it (software)
IT wants a lower-case Login built from Email, while HR needs the Email column left exactly as typed.
- beginner~5 minPro
Duplicate a column before changing it (hospital)
IT wants a lower-case Login built from Email, while HR needs the Email column left exactly as typed.
- intermediate~7 minFree
Choose columns when one of them is optional (kite shop)
Some exports include a Notes column and some don't. Keep the same four columns either way.
- intermediate~7 minPro
Choose columns when one of them is optional (bakery)
Some exports include a Notes column and some don't. Keep the same four columns either way.
- intermediate~7 minPro
Choose columns when one of them is optional (bike rental)
Some exports include a Notes column and some don't. Keep the same four columns either way.
- intermediate~8 minFree
Remove every helper column whose name starts with tmp_ (survey)
The export tool adds working columns named tmp_…, and their number changes. Remove all of them by rule.
- intermediate~8 minPro
Remove every helper column whose name starts with tmp_ (payroll)
The export tool adds working columns named tmp_…, and their number changes. Remove all of them by rule.