Forum Discussion
Loading results back into excel
Hi Mal_B,
From your description, could you want to union two excel tables with same numbers of rows? If so, you could try to refer below steps in query editor:
Sample data:
Transpose Table 1 and delete the first row, then transpose the Table 2:
Append the Table 1 and Table 2 as new table. When you have add columns in table 1 or table 2, it could also work.
You could also download the pbix to have a view:
https://www.dropbox.com/s/38vrt4clrh0rwtt/Loading%20results%20back%20into%20excel.pbix?dl=0
Regards,
Daniel He
No I don't want to the union two tables. These are two entirely different tables with two different sets of headings and different numbers of columns (and row labels). As stated in my question, I want to two tables to appear, one below the other, on the same excel worksheet. They are different tables showing different things, but the user needs to see both tables at the same time to get a view of the info they need.
You might just be able to make it work with a Union query but it would be hugely messy involving the following steps:
1. Demote the headers in the table 1 to a row
2. Add new headers to table 1 which are A,B,C ... up to the relevant number of columns
3. Create a new total query (because you won't be able to total the final output) for table 1
4. Create a new query which generates, say, four, entirely null rows (to space the two tables)
5. Repeat step 1 with table 2
6. Repeat step 2 with table 2
7. Repeat step 3 with table 2
8. Union the tables created in steps 2,3,4,6 and 7 to create a new table
9. Strip the A,B,C header row off the table
10. Push the table back into Excel and use Excel's conditional formating to find the original header rows and the total rows (because these are now simply normal rows in the unionised table) and make them appear as if they were header and total rows
But frankly this is a mess and wouldn't allow any user to sort or filter either of the two individual tables - which they may well want to do.
Similarly, and easier than the above, I could put the two tables into different hidden worksheets and then use a dynamic picture of each to show the results on a single sheet. The problems with this are, like the above, it does not allow the user to filter or sort the tables (as they are only pictures) and the pictures don't dynamically resize when extra columns are added.
So, back to my original question, how do I get two separate tables on the same worksheet, one above the other, when either table can have a variable number of columns after a pivot? Surely it must be possible to have two G&T pivot tables on the same worksheet?