Forum Discussion

Laosi's avatar
Laosi
Copper Contributor
May 25, 2025

BROKEN FILTER FORMULA AFTER UPDATE QUERY

Can someone please help, my filter formulas seems got broken after i update my query.

I make table in several sheets cointaining filter function, the purpose is to find the MAX and MIN of data percentages from certain range of dates with different criteria

this is the formula

=IF(COUNTIFS(財務比率[CODE];[@CODE];財務比率[Date];"=0");"NO DATA";MAX(FILTER(財務比率[Assets, b.IDR
T-1 %];(財務比率[CODE]=[@CODE])*(財務比率[Date]>=歴史[@[MONTH
L]])*(財務比率[Date]<=歴史[@[MONTH
BB]]))))

財務比率 sheets is the source data

歴史 sheets is the query table containing date criteria

everything is fine until i updated query 歴史, adding new row "CENT 4."(Column NEW CODE) and now most of my MAX and MIN formula return error #CALC! with explanation "Empty Array.

I`ve checked file sources for 歴史, check power query got no error, and tried rewrite the filter formula, still no luck

i also notice my file size got smaller from 51MB to now 46MB, suggesting there is data loss.

This had been several days now, please help 🙏

Trend Up History Broken Filter

 

Resources