Adding Column to Source Table Breaks the Filter() Function

Question

Hi Community,

I am looking for advice on fixing broken Filter function. This function: FILTER(array,include,[if_empty]).

• The source array of the filter function is a table using the TableName[ColumnName] format
• Similarly the include is matching another field in the table.

Like this: =FILTER(tablename[company name], tablename[business type]=$b$4,) -please forgive any syntax errors in the example.

The source table itself is from a power query (an append query).

The filter function is completely breaking when the query result updates and adds a column to the left side of the existing columns used in the filter. All the named ranges will get offset by the number of added columns. For example, if the original table was:

CompanyName, Address, Business Type, Supplier Code

and the updated table after query refresh is:

NewField, CompanyName, Address, Business Type, Supplier Code

Then the original formula:

=FILTER(tablename[company name], tablename[business type]="$b$4,)

will automatically shift to the left and the names change like this:

=FILTER(tablename[NewField], tablename[Address]="$b$4,)

I cannot understand a scenario where a change in the source table will rewrite the formulas. Note that the problem even happens when using this format:

=FILTER(tablename[[company name]:[company name]], tablename[[business type]:[business type]]="$b$4,)

The worst of it is, that even if I edit the formula back to the original field names, it won't work. Something like an index in the background of the worksheet seems broken where it prefers some improperly updated index number over the actual text visible in the formula.

If I retype the correct formula from scratch on a different worksheet in the same workbook it will work as expected.

Right now I can manipulate the query to move the added field to the end of the table, but this means I need to check that no one has modified the source table before any updates.

Thanks in advance for your inputs.

Answer 1

I did a small test with indirect and it didn't seem to work, but I will do a more thorough test. Thanks.

I did find another shortcut - I used 'search and replace' to change the table name in all formulas (6000+ replacements) to a static copy of the table, updated the query, then did search and replace back to the original table name. In the future I plan to build with indirect, but thankfully search and replace worked!

Answer 2

Try

=FILTER(INDIRECT("tablename[company name]"), INDIRECT("tablename[business type]")=$B$4, )