Hi Paul,

The modern way to get data form an external workbook is with Power Query. With Power Query you can merge/append data in multiple tables and then analyse it in a PivotTable in the same workbook or an external workbook.

I don't have a tutorial on the blog on that scenario, but it is covered in my Power Query course.

Mynda

Hi Mynda,

Normally, that approach would work well. But in this case, I created an External Connection Only through Power Query and then created a Pivot Table using that external connection, because the source table is huge and I didn't want to load it into my destination file. But now I want to add more tables to that Pivot Table, whose total rows are well over a million if combined. I thought the answer was in the "More Tables" option in the Pivot Table field editor, but I can't get it to work the way I want.

Paul

Hi Paul,

Ok, I misunderstood because you didn't mention that you'd used Power Query to get the data initially, you just said you used an 'external connection' which I took to mean you simply connected to a Table in another file. I think there is still some information missing about how you got the data and connected to it for me to fully understand.

I didn't suggest for you to load the data into a Table in the worksheet in your destination file. You can get the data with Power Query and 'Close & Load' as a connection only and analyse in a PivotTable with the data never touching the grid. Or you can add it to the Data Model as a connection only and analyse in a Power Pivot PivotTable. Either way the data doesn't go to the Excel grid. 

What I don't understand is what you mean by 'add more tables to that PivotTable'. Do you mean 'add more data to the PivotTable source data from other tables'? If so, the method is still to use Power Query to consolidate (merge/append) the tables into one. You either do that in the original query file, or you create a new query that consolidates all of the tables and then 'close & load to' 'connection only' and 'PivotTable' or 'add to data model' and create a Power Pivot PivotTable.

I hope that makes sense.

Mynda

Mynda, I was wrong--the reply you sent provided the key. I had four tables to append. I created a Connection Only for each of them, and then chose Combine Queries from the Get Data tab. When that's appended (in my case) I created yet another Connection Only from it. Now the newly created append query had all four tables' worth; I then created a new Pivot Table using this append query as the external connection source.

It's a different way of thinking to what I'm accustomed. This will be valuable as a skill to handle large data sets--break them into separate tables, then merge or append them as needed.

Thanks!

Paul