Forum Discussion
Adjust a formula to ignore hidden/filtered rows of data
Thanksdjclements , that's cool! It's literally my first time encountering such things in Excel, but your example is quite easy to understand. It's much simpler than the VSTACK version. However, I would like to avoid use of IFERROR - just in case, for troubleshooting purposes. Errors are easier to spot than digging into the formula to find out why it's showing 0... Your version shows #CALC! at the FILTER step if no rows are visible (FILTER returns an empty array), hence the need for IFERROR. Neither UNIQUE nor ROWS will work with an empty array. Is there a way to test an array for emptiness? E.g., something like:
=LET(array,
FILTER(names[Name], BYROW(names[Name], LAMBDA(r, SUBTOTAL(3, r)))),
IF(<array is empty>, 0, ROWS(UNIQUE(ARRAY)))I understand that FILTER already returns a calculation error, but was wondering if something like the above is possible at all...
SergeiBaklan's version also is elegant - even i could understand it, with a bit of documentation read (: However, it also shows #CALC! if no rows are displayed... I'm afraid, using IFERROR is the only option to return 0 in both your variants. VSTACK version is free from this limitation, but it's waaaaay less performant than both alternatives. On a dataset of 22k rows, it takes ages for the original formula to calculate the subtotals, while both alternatives finish in under 1 second (basically, the time needed for Excel to display filtered data).
Thank you guys, you expanded my knowledge of Excel beyond the horizon! (:
g0ldfinch The IFERROR function is a reasonably safe method to use in this scenario, because the only plausible error that can be returned by the ROWS function is #CALC!, which is a result of the FILTER function returning an empty array. However, if you want to avoid using IFERROR altogether, use the LET function to define the include argument first, then test the results with the OR function to make sure at least one record meets the criteria. For example:
=LET(
incl, BYROW([Name], LAMBDA(r, SUBTOTAL(3, r))),
IF(OR(incl), ROWS(UNIQUE(FILTER([Name], incl))), 0)
)
Note: if the [Name] column happens to be a calculated column that also contains errors, and you don't want those errors to be included in the count of unique records, replace SUBTOTAL(3, r) with AGGREGATE(3, 7, r) to ignore hidden rows and error values.
Regarding the SUBTOTAL function also counting what seems to be "blank" cells produced by Power Query, this is not a bug per se, as the query is returning empty strings instead of null values. To solve this, you could just add another step to the query that replaces empty strings with null values in order to return truly blank cells in the query results. In the Power Query Editor, select the desired column, then go to Transform > Replace Values. Leave the "Value To Find" box blank, and type null in the "Replace With" box.
I hope that helps. Cheers!
I'm not sure, that's mainly performance issue. If performance is not the point, do as as it is better in maintenance for you.
Thanks, SergeiBaklan .
> Better to apply FILTER() once to shorter array.
What i meant by applying the filter in LAMBDA is not the use of the FILTER function but rather adding an extra condition joined with the visibility test by AND. Are there any disadvantages here?
null - depends on scenario of course. Any placeholder works with same data type (text, number, etc.). null, which actually means "missing", is more universal.
filter. TOCOL() is extremely fast; FILTER() is relatively slow. Better to apply FILTER() once to shorter array.
Thank you,SergeiBaklan - your advice is really helpful. I don't understand why so many tutorials on PowerQuery have this step of replacing nulls with empty values... Especially, considering that "nulls" in PQ are not the same as nulls in real DBs - that is, comparison operators treat nulls the same way as other values, so there is no real need to get rid of them other than visual aesthetics (:
As for your formula, why use FILTER as an extra step instead of implementing the filter inside the LAMBDA? From the programming point of view, yes, we encapsulate each task in its own block. However, wouldn't it be more performant to keep the condition in the LAMBDA? Or it doesn't matter, Excel will optimise that internally, but it allows us to keep the code clean?
As a comment, in general you shall not "use the opposite transformation before loading the query data." Within Powe Query is usually better to keep nulls, not empty values. And use lists to combine fields with nulls with other values. That's more reliable.
However, if empty texts are returned with Power Query and you don't like to count them, simple filter works
=LET( u, UNIQUE(TOCOL(MAP( [Name], LAMBDA(v, IF(SUBTOTAL(3,v), v, NA() ))),2)), ROWS(FILTER(u, u<>"")) )Wow... I didn't realise that. I used to replace nulls with empty strings, but it never occurred to me that i should use the opposite transformation before loading the query data. Thanks! That makes a lot of sense. Using OR on an array also is ingenious. Once you gave that example, it struck me that OR checks values of an array containing 0 and 1, and if at least a single 1 value exists, it returns TRUE... I'm sure it does lazy computations so it must be quite efficient as well. So elegant! *Sigh* The more i learn the more i realise that i know nothing...
P.S. I added a ReplaceValue step to the PQ - it solve the issue and rid of the necessity to check values for an empty string. Thanks a lot - will keep that in mind in the future.
