One of the first things I learned about Power Query—and a rule I still follow religiously today—is to not select a column and delete it. To build a truly resilient dashboard, you must instead tell the editor what to keep, not what to remove. Here’s why Remove Other Columns is your new best friend.
Most Power Query errors aren’t bugs—they’re bad instructions
When we clean data in Power Query, our first instinct is to right-click the junk columns and click “Remove Columns.” It feels efficient, but it actually creates two traps that will catch you out down the line.
Suppose you send a nine-column table to the Power Query Editor (via Data > From Table/Range) and delete six of them by right-clicking the headers and selecting “Remove Columns,” leaving only Product, Units Sold, and Profit.
The next day, a coworker adds a new Notes column to the source spreadsheet. Because you only told Power Query to delete the original six, this new, bulky column flows straight into your dashboard as soon as you refresh, cluttering your report.
Related
5 everyday actions that Power Query does better than regular Excel tools
Replace manual Excel tasks with conditional columns, smart merging, the unpivot tool, and more.
After fixing that issue, you refresh your query the next day and are met with an error message. This is because another coworker renamed the Department column to “Category” at the source—a column your query was explicitly told to remove. Power Query follows its instructions rigidly: if it can’t find a column it was told to remove, it throws an error and refuses to finish the update. Your dashboard stays stuck on old data until you fix the step.
By shifting from deleting columns to defining what to keep, you ensure the query only looks for essential data and ignores everything else.
How to correctly define your data “allow list”
To avoid these traps, you need to change your perspective. Instead of identifying what to throw away, you should focus on the specific fields that your report requires to function. In this case, those must-have columns are Product, Units Sold, and Profit.
In the Power Query Editor, hold the Ctrl key and click the “Product,” “Units Sold,” and “Profit” headers. Then, right-click any of those three selected headers and click “Remove Other Columns.”
If you’re working with a massive dataset containing lots of columns, scrolling horizontally while holding Ctrl is a nightmare. Instead, in the Home tab, click “Choose Columns,” and use the checklist that appears to tick off the headers you want to keep.
The Applied Steps pane on the right now shows a new step labeled Remove Other Columns, and the underlying M code explicitly lists the three columns you want to keep:
=Table.SelectColumns(#”Changed Type”,{“Product”, “Units Sold”, “Profit”})
This one line of code changes everything. Instead of subtracting columns, that function explicitly defines the schema of your dataset. Power Query now knows exactly what your report depends on—nothing more, nothing less.
Once you’ve isolated those columns, click the top half of the split “Close & Load” button in the top-left corner of the Editor window to send the cleaned data to a new worksheet.
Even though the cleaned report looks the same as when you used the Remove Columns workflow, it’s more robust. To prove this, add a new column to your source table and change the header of one of the columns that isn’t part of your report. Then, head to the worksheet with your refined dataset and click “Refresh” in the Query tab. You’ll see that nothing changes: Power Query skips over the new column and modified header, focusing only on the three columns you told it to keep.
Related
Clean up messy Excel data fast with fuzzy matching in Power Query
Merge lists even with typos and inconsistent names. Tune the similarity threshold, use a transform table, and audit results before loading.
This workflow improves performance and clarity
As well as making your Excel dashboard more resilient, this habit makes your Excel workbooks significantly faster. By using Remove Other Columns as early as possible in your query, you act as a filter that stops unnecessary data from ever flowing through the rest of your query. This becomes even more important if your query feeds the Data Model or a PivotTable, where every unnecessary column increases memory overhead.
If your source file eventually grows from 10 columns to 100, a standard Remove Columns step appearing later in your query may force Power Query to carry all that extra data through earlier transformations before finally deleting it. However, using Remove Other Columns isolates only the fields you need from the outset. This reduces memory usage and can noticeably improve refresh performance, especially when working with large Excel tables or queries that don’t support query folding.
Related
7 Ways to Speed Up Your Excel Spreadsheets
Don’t twiddle your thumbs waiting for Excel to respond.
This approach also makes your work self-documenting. Anyone auditing your Applied Steps can select that early step and see exactly which columns the entire report relies on. Six months from now, when you revisit the file, you won’t need to reverse-engineer your logic—the required columns are declared explicitly in one clean step.
By choosing what to keep rather than what to trash, you’re adding one of the many must-know Power Query workflows to your toolkit. You’re not only building a dashboard that doesn’t break whenever someone tweaks a spreadsheet, but you’re also keeping your workbook lean and efficient as it grows.
OS
Windows, macOS, iPhone, iPad, Android
Free trial
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
