Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mal_B
Regular Visitor

Loading results back into excel

I have two queries which are the results of pivotting data.  They each have a maximum of 10 rows (plus header) but a variable number of columns depending on the input data.

 

I would like to display them with one above the other in a single Excel worksheet.

 

However loading the pivot-bi tables back in to Excel seems to be implemented in a such a way that when a new column is added to a table, a whole column is added to the Excel worksheet; similarly if a column no longer exists when the pivot table is refreshed, a whole column is deleted from the Excel worksheet.    (This is different from added columns into normal Excel tables where room is made for additional table columns by moving only cells to the right of the table one further column right etc).

 

Adding or deleting a whole column impacts on the other table in the worksheet.  If a column is added to the top table, for example, a blank column appears in the lower table.  Attempting to reload the top table with fewer columns than before (and fewer columns than the second table) causes an error.

 

Is there any way of loading two such tables (fixed number of rows, variable number of columns) into the same worksheet vertically above each other which does not cause errors when the number of columns changes on refresh?

 

Thanks

2 REPLIES 2
v-danhe-msft
Employee
Employee

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:

1.PNG

Transpose Table 1 and delete the first row, then transpose the Table 2:

2.PNG

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.

3.PNG

 

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

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.