Forum Discussion
Connecting to a Power Pivot model from another Excel workbook
Hi,
A simple question: is it possible to connect to an a Power Pivot data model within an Excel workbook from another workbook?
Simple scenario: we have set up a data model with the help of Power Pivot in Excel and we would like to create a number of reports based on this model in various workbooks. The only alternative we currently know is taking the one Excel workbook with the data model and then creating the reports *within the same workbook* (using CUBEVALUE(“ThisWorkbookDataModel” formulas). Is there a way to change the connection from “ThisWorkbookDataModel” to the connection of the workbook with the data model in it
If not, what would be the alternatives (note: we are not using Power BI Desktop or the Power BI Service)? Maybe publish the Excel workbook with the data model in it to SharePoint?
Any suggestion would be much appreciated. Thanks, Balazs
Hello Anonymous
the only possibility to access the data model itself it's only possible when you have opened the other Excel with the datamodel (if even - i know it's possible with Power BI). But this doesn't suit the most business scenario.
The way I've solved such issues to create in your excel as many pivots as you need in other scenarios and then access the data of the pivot table. However you have to update the excel-file manully, every time your data model changes.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
9 Replies
Hi Anonymous ,
I am not clear about your requirement, did you mean that you want to change model to another workbook in original workbook? If so, you could try to change the datasource in power query directly like below
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi there Zoe,
Thanks very much for the reply - and apologies for not being clear.
Yes, I know how to connect to the DATA in another workbook - but is there a way to connect to the entire DATA MODEL in another workbook?
Our scenario: in order to avoid crashes (which, alas, do happen...), we decided to have the source data in a table in Workbook 1. In Workbook 2, we then connect to Workbook 1 the way you described and import the data into Power Pivot, setting up the required relationships for our data model. Now we would like to reference to this data model (which resides in Workbook 2) from Workbook 3, Workbook 4, Workbook 5, etc.Through research, we found this link, mentioning Analysis Services for Power Pivot; however, this would require SQL and SharePoint Server to be set up (if I understood correctly).
My question is, whether there is a viable alternative to connect to a data model in an Excel workbook from /(an)other workbook(s).
Thanks again,
Balazs
Hello Anonymous
the only possibility to access the data model itself it's only possible when you have opened the other Excel with the datamodel (if even - i know it's possible with Power BI). But this doesn't suit the most business scenario.
The way I've solved such issues to create in your excel as many pivots as you need in other scenarios and then access the data of the pivot table. However you have to update the excel-file manully, every time your data model changes.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHello Anonymous
i don't know if you got me right. The solution i've implemented is working well.
Just use PowerQuery of Excel to get the data of the central excel-file and you can automate everything.
So just create pivot-tables that contain every data you need and thats it
What do you think about it?
Jimmy
Connect a Power BI file to the Excel file containing the data model. Then create a new Excel file and connect it to the Power BI file.
The solution has two downwards when dealing with big data: 1) nowadays databases can easily reach the ~1M rows threshold; 2) even if the limit is not reached, the workbook performance becomes extremely bad.
