Forum Discussion
Laosi
May 25, 2025Copper Contributor
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
1 Reply
Sort By
- Patrick2788Silver Contributor
You can resolve the #CALC! by providing a value in FILTER's if_empty argument: