Join dynamic arrays (or really tables, or dynamic references)

[shibbius]

Hello all!

Been a long while since I posted but this is a head scratcher for me.

I have a series of tables (call them table1, table2, table3) that all follow the same column format. I'm trying to figure out how I can combine FILTER() results from each of those tables to be presented in a dynamic array. So if I have:

=FILTER(table1,table1[Health]="Poor") in A1
=FILTER(table2,table2[Health]="Poor") in B1
=FILTER(table3,table3[Health]="Poor") in C1

My intended result would be a single dynamic range of {A1#;B1#;C1#}.

I've thought about using helper tables as described above, joining all of the tables into one master table to reference with a single formula, potentially concatenating those formulas to avoid the helpers, and just about every other technique I can think of but my googling skills have failed me.

Any ideas from the crew?

Thanks!

 

[shibbius]

The more I think about this the more I think that maybe the move, if it's possible, would be to define a new table as such:

tableMaster made up of {table1;table2:table3}

So you could write this formula (and others) as follows:

=FILTER(tableMaster,tableMaster[Health]="Poor")

Or at least, that would be the most elegant solution that I can see. Not sure if it's possible to create a master table of other tables, though.

 

[sandy666]

append all tables with Power Query then do what you want with the result

 

[sandy666]

ops, you are on MacOS, so ignore post above

 

[shibbius]

Thanks! I’m hoping there’s a formula solution for this too.

 

[shibbius]

I've wound up looking into how to do this in our BI tool since there doesn't appear to be a solution using formulas or dynamic arrays.

If that changes, or I figure out how to do it, I'll come back and post about it!

 

[sandy666]

Power BI contain Power Query so you can append all tables then do what you want with the result
but I don't know how PBI will work on Mac