Dynamic arrays and filters

[Baccma]

I have an Excel table (which is created from third party data via Get & transform.
This is then further processed using a dynamic array.
If however I filter the table, the dynamic array still uses the full unfiltered table.

How can I get a dynamic array to work only the filtered entries and ignore those which are hidden by the filter?

[#2]

@Baccma It could work if you add a column that checks if the row is visible (1) or not (0), using the AGGREGATE function (function number 3 for COUNTA, option 5 to ignore hidden rows. Then, use FILTER to create a dynamic array of only the visible rows from the table. The attached file contains a simplified example.

[#3]

@Riny_van_Eekelen 

Thanks I am aware of that approach but it is cannot be used here.  That's why I made the point that the input is from table created from  "Get and transform".  It is not possible to add a column to this which shows whether the rows in this table are hidden or visible.  

It would be great if you could use G&T to add a formula column, but if you try (even starting the text in the column with an = sign) Excel still interprets the column as a text column and fails to evaluate it.  

[#4]

@Riny_van_Eekelen   The furthest I managed to get using the AGGREGATE approach was to set up a helper table using a dynamic array,  then add an aggregate column to that using INDIRECT to point to the original table - see attached example.  However AGGREGATE does not seem to work with dynamic arrays and so, if I add an extra row to the Input table, the column containing the AGGREGATE function in the dynamic array does not expanded.  I have also tried the old {} approach and that does not work either.  In the example, add a row to the top input table and you will see that whilst columns A and B of the output table expand properly, column C does not.

I therefore hit a brick wall with that approach and any suggestions would be hugely appreciated.

[#5 · Accepted solution]

@Baccma Not sure I follow. Added an example in your sheet. Isn't that what you described?

[#6]

Ah, now that is interesting. When I added the AGGREGRATE column to the Get & Transform output, it got deleted when the G&T refreshed, It doesn't in your example. Now I need to find out why - the actual spreadsheet is much more complex obviously and the problem might be in some of the code. But thanks for clearing that point up. It looks as if I can get the visible column to stay where it is meant to be, the problem's solved. Thanks again.