Hi
I'm working with a number of tables in Excel, some of which are very large. I need to pivot on the data to create pivot tables and charts. I'm on Excel 2016. I've completed the Power Query course and am half way through Power Pivot.
I don't want my data in the same workbook that has all my pivots or the file would be huge. I can't really see how to use power pivot to keep the data separate but have it loaded to the data model. Linked Excel tables seem to be in the same workbook?
So for now I'm using power query to create my data model by creating a connection only from my tables. Seems to work fine but as I don't need to clean the data I don't know if this is the best way to do it. Is there a way to use power pivot to create my data model but keep the tables in a seperate workbook or am I best to keep using power query for that?
Hope that makes sense!
Hi Sharleen,
In session 3.05 I cover how to get data into Power Pivot from external Excel files. This way your data can be in a separate Excel file and then you just load it to Power Pivot in the file you want to do your analysis in.
That said, you can use Power Query to get the data from the external file and load it to Power Pivot/Data model too. If you need to add any calculated columns in Power Pivot, or filter data out, then it would be more efficient to do this in Power Query before loading to Power Pivot. If not, then there's no need to use Power Query to get the data.
Mynda
Great thank you. I'll revisit that topic.
If my Power Query exists in a separate workbook can I use it in Power Pivot without first adding that query to the current workbook? I am having trouble getting a large Power Query that I am using in other sources into Power Pivot.
Hi Cheryl,
Queries are attached to the current workbook only. If you want to get the same data in another workbook then you can copy the query across; in the Queries pane, select the query > CTRL C to copy. In the new workbook, create a blank query and paste in the copied query.
Upon 'Close & Load' choose 'add to data model'.
Mynda
Got it - that is what I did - just wondered if there was another way. Thank you!
