Power Query Problems

Fix Excel FILTER Formula Breaking After Power Query Adds a Column

Huma Ashraf Ch Sep 30, 2026 869 views

Question details

The user's Excel FILTER formula utilizing structured table references points to incorrect columns after a Power Query refresh adds a new column to the left of the table.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Refreshing a Power Query that automatically inserts a new column to the left of an existing referenced table.
Observed behavior: Structured table references in the FILTER formula automatically shift, causing the formula to pull data from the wrong columns and breaking the expected output.

Before you start

Ensure your Power Query data source is correctly configured and save a backup of your workbook before applying structural formula changes.

Solution 1Recommended

Use the INDIRECT Function to Lock Structured References

Wrap your structured table references in the INDIRECT function to prevent Excel from automatically shifting the column references when Power Query adds new columns.

By default, Excel dynamically adjusts formula references when columns are inserted or deleted. By converting your structured references into text strings using the INDIRECT function, you force Excel to always look at the specific column name, regardless of where it shifts during a Power Query refresh.

Select the broken formula cell

Click on the cell containing your current FILTER formula that points to the incorrect columns.

Modify the array reference with INDIRECT

In the formula bar, replace the standard table reference (e.g., tablename[company name]) with the INDIRECT function formatting it as a text string: INDIRECT("tablename[company name]").

Modify the criteria reference with INDIRECT

Apply the same logic to your filter criteria. For example, change your formula to look like this: =FILTER(INDIRECT("tablename[company name]"), INDIRECT("tablename[business type]")=$B$4, "")

Apply and test

Press Enter to save the formula. Refresh your Power Query to confirm that the reference no longer shifts when new columns are added.

Use the INDIRECT Function to Lock Structured References

Performance Consideration: INDIRECT is a volatile function, meaning it recalculates every time any change is made in the workbook. Use it judiciously to avoid performance drops in highly complex spreadsheets.

Solution 2

Temporarily Replace the Table Name During Refresh

Create a static copy of the table, update the formula to point to it before refreshing the query, and switch it back afterward to prevent reference shifting.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

If you frequently encounter formula referencing issues or heavy resource usage in Excel, consider switching to WPS Office. It provides a lightweight, highly compatible, and user-friendly spreadsheet environment that handles complex data effortlessly.

1. Download and Install: Visit the official WPS website to download and install WPS Office for free.
2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx file directly.
3. Manage Formulas with Ease: Enjoy a familiar interface to edit, manage, and calculate your FILTER and array formulas seamlessly.

Completely free to download and useSeamless compatibility with Microsoft Excel (.xlsx, .xls) formatsLightweight design ensures fast calculation and query speedsFamiliar interface requiring zero learning curve for Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Why do structured table references shift when a Power Query refreshes?

When Power Query inserts a new column to the left of an existing table, the spreadsheet software treats it as an inserted column. By design, this automatically shifts existing formula references to the right to maintain their spatial relationship, causing them to point to incorrect columns.

Does the INDIRECT function slow down spreadsheet performance?

Yes, INDIRECT is a volatile function. This means it recalculates every time any change happens in the workbook. In large workbooks with thousands of INDIRECT formulas, this can cause noticeable performance drops and increased calculation times.

Can I fix this by changing Power Query settings instead of the formula?

While you can manage column structures within the Power Query Editor to append new data to the right instead of the left, if the source data strictly dictates a new column on the left, the native formula shifting will still occur. In those cases, adjusting the formula via INDIRECT is the most reliable method.

Excel & Data Problems