Forum Discussion
Can you use table structured references with a 3D reference Same column through multiple tables.
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.
- That's a short term way to go. Thanks for updating
If you need to sample to consolidate your tables with PQ, feel free to ask
Nice day...I would really appreciate an example of a power query.
The main student worksheet has student numbers in column "a". Column "w" holds the result of the "xlookup" from all of the class attendance worksheets.
The subject worksheets (class attendance) have the student number in column "c" with class term attendance total in column "h", and individual attendance date columns "i" to "t" (which are summed in "h"). So the lookup on the main student worksheet (column "w") looks through each class sheet for the student, and adds the attendance (if any) to their attendance total in column "w" of the main studen worksheet.
I hope this is enough info for you to give me an example of a power query. Thank you for your time.