Forum Discussion
Can you use table structured references with a 3D reference Same column through multiple tables.
Hi Debs_au
Given that your tables appear to be consistenly named (Table01, Table02...Table20) with exactly the same columns it would be pretty straightforward to consolidate them all as a big Table with Power Query. You would then perform your XLOOKUP on that big Table
Is this an option for you? Do you need a sample?
Note: if you keep the same naming convention for your tables, the query can auto. pick any additional table, i.e. Table21, Table25...
- Hi L z,
I was thinking along those lines to use a Pivot table. Is Power Query the same thing or different?Hi Debs_au
In short: Power Query & Pivot Tables are 2 different things but the first can help the second...
Not clear to me what you ultimately want to achieve. However, assuming you want to build a Pivot Table from the data in 20 Tables, in any case you need Power Query to "take" your 20 Tables and consolidate them as 1 big Table that would be the Source for your Pivot Table
Note that if you build a Pivot Table out of the Power Query query, you don't necessarily have to load the big Table on a worksheet
Hope this clarifies things a bit
I ended up just using XLOOKUP with structured table references with '+' between them and using a 'Total' line at the end of the main table. Although the formula was a little unwieldy it worked well and will do for the moment until I read up on Power Query and can update what I have done to make it more elegant.
