SOLVED

Dynamic arrays and filters

%3CLINGO-SUB%20id%3D%22lingo-sub-2855147%22%20slang%3D%22en-US%22%3EDynamic%20arrays%20and%20filters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2855147%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20Excel%20table%20(which%20is%20created%20from%20third%20party%20data%20via%20Get%20%26amp%3B%20transform.%3CBR%20%2F%3EThis%20is%20then%20further%20processed%20using%20a%20dynamic%20array.%3CBR%20%2F%3EIf%20however%20I%20filter%20the%20table%2C%20the%20dynamic%20array%20still%20uses%20the%20full%20unfiltered%20table.%3C%2FP%3E%3CP%3EHow%20can%20I%20get%20a%20dynamic%20array%20to%20work%20only%20the%20filtered%20entries%20and%20ignore%20those%20which%20are%20hidden%20by%20the%20filter%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2855147%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2855475%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20arrays%20and%20filters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2855475%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F581579%22%20target%3D%22_blank%22%3E%40Baccma%3C%2FA%3E%26nbsp%3BIt%20could%20work%20if%20you%20add%20a%20column%20that%20checks%20if%20the%20row%20is%20visible%20(1)%20or%20not%20(0)%2C%20using%20the%20AGGREGATE%20function%20(function%20number%203%20for%20COUNTA%2C%20option%205%20to%20ignore%20hidden%20rows.%20Then%2C%20use%20FILTER%20to%20create%20a%20dynamic%20array%20of%20only%20the%20visible%20rows%20from%20the%20table.%20The%20attached%20file%20contains%20a%20simplified%20example.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2856460%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20arrays%20and%20filters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2856460%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20I%20am%20aware%20of%20that%20approach%20but%20it%20is%20cannot%20be%20used%20here.%26nbsp%3B%20That's%20why%20I%20made%20the%20point%20that%20the%20input%20is%20from%20table%20created%20from%26nbsp%3B%20%22Get%20and%20transform%22.%26nbsp%3B%20It%20is%20not%20possible%20to%20add%20a%20column%20to%20this%20which%20shows%20whether%20the%20rows%20in%20this%20table%20are%20hidden%20or%20visible.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20great%20if%20you%20could%20use%20G%26amp%3BT%20to%20add%20a%20formula%20column%2C%20but%20if%20you%20try%20(even%20starting%20the%20text%20in%20the%20column%20with%20an%20%3D%20sign)%20Excel%20still%20interprets%20the%20column%20as%20a%20text%20column%20and%20fails%20to%20evaluate%20it.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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?

5 Replies

@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.

@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.  

@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.

best response confirmed by Baccma (Occasional Contributor)
Solution

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

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.