Getting a table to dynamically match a variable range's row count


Hello Excel wizards,


I find myself doing something which feels really clunky, and I'm hoping there's a smarter solution.


I import a filtered range of values using a FILTER() call (let's say in A2) which will populate columns A2:Bx. The row depth (x) is variable, depending on Power Query updates of the source data that FILTER() is acting on.


Next to this data, in C2:Dx (let's say) I want to construct a table that adds new calculations based on the values in the FILTER() range. I'll attach an example workbook. (Why a table? Because it's convenient for formula editing, and this table itself might be referred to in other functions elsewhere...)


How can I get the table in C2:Dx to dynamically match the row count (x) of the filtered range in columns A2:Bx? I don't know any way of doing that. You can't use a FILTER() function inside a table (right?)*, which is why I'm just placing my table (C2:Dx) next to the FILTER() output.


My "solution" (shown in the attached workbook) is to build a table with about twice as many rows as I anticipate ever needing, and to use some kind of masking formula in the table cells that starts with IF(A2="","", ...) to make sure that the table rows below the FILTER() range don't fill up with #N/A! errors. But this feels like a really dumb solution, and of course if I ever guess wrong, and my FILTER() range is longer than my table, then I get a bunch of values that don't calculate at all.


I can tolerate this in something I build for myself, but it's an awful thing to leave in a solution built for a client!


I would love a better way of getting my table to dynamically match the row depth of my FILTER() ouput! Or indeed just a better solution to the whole problem! Any ideas?




*Note, in my attached simple example, I am only using FILTER() to zap a column, which I know I could do differently. In the real world this is liable to be an embedded FILTER(FILTER()) call acting on both columns and rows of the source data, so I can't easily see how to make that into a column formula in the table itself, for example...

6 Replies

@ColinJHarrison Why not continue within PQ. The information in the dynamic array in A2:B6 can easily be created from the SourceData. And then you can add a column (in PQ) that extracts the lengths from the Name columns and merge merge with the Ages table to pick-up the ages based on the Names columns in both tables.



Right... Hadn't thought of that. Currently I PQ the source data into a hidden sheet, and then use shenanigans like this to generate a range of info sheets that the users want. But I could just compose the user sheets directly with PQ. Hmm... Some of the sheets I need to generate combine info from different PQ imports (so, my FILTER() function might be acting on one source, but my table formulas on a different source...) Can I make a PQ combine data from multiple sources? 

And, can you apply formatting to a PQ input table (that won't get reset on update)? I've never tried that.
best response confirmed by ColinJHarrison (Contributor)


"Can I make a PQ combine data from multiple sources?"


Short answer, Yes! You can merge information from multiple sources. A merge works like Excel's lookup functions, but without their limitations. Just make sure that tables to be merged have at least one column in common.


"And, can you apply formatting to a PQ input table (that won't get reset on update)?"


Yes! When you have loaded a table from PQ into an Excel sheet, you most likely get a green banded table. You can change the style, column formats and table options like auto-resizing columns or not.


New things to learn! Thank you yet again Riny!