How to disconnect power query from power pivot model?

I have modified some data in power query. I have then loaded this data into a power pivot model (connection only).

I now want to be able to edit the model in power pivot but I am only allowed to edit using the power query interface.

Is it possible to copy the data that's in the power pivot and disconnect the model from power query all together. I've tried disconnecting but this will remove the model from power pivot.

Note: I need to use power query for preparation reasons and must use "connection only" as loading as a table would exceed excels row limit.

Thanks
Mike
mikes6058Asked:
Who is Participating?
 
ProfessorJimJamCommented:
in that case then duplicate the worksheet, select the table then right-click in the copied table and select "unlink from data source" then loan the powerpivot from the unlinked table.
0
 
ProfessorJimJamCommented:
Hi Mike,

i am not sure if i fully understood the problem, because it would then depend on the version of Excel you are using.  If you are using Excel 2013 then there was a patch update released last year that fixed the problem, if you are using Excel 2010 then i assume this problem still exists, so if you have excel 2010 then you may be able to fix the issue with the workaround here

Miguel Llopis of the Power Query team provides a workaround in Microsoft page. and here
0
 
mikes6058Author Commented:
Hi Professor,

I have excel 2013.

I understand the reasons for the patch update however it doesn't resolve my problem.

I want to disconnect my data model from power query all together once its been loaded into power pivot. Effectively the model would then work like any other model where I can make changes to the model from the "manage" power pivot screen.

Mike
0
 
ProfessorJimJamCommented:
ok, can you try the following. if i understood correctly. you want to disconnect your data model from power query to do that here are the steps.

first disable Power Query  by going to the COMM Add-Ins and disablign it. then on the file with data go to
run Document Inspector and clean XML data. to access Document Inspector it is under FIle then Info then on the dropdown under "Check Issues"
0
 
mikes6058Author Commented:
Hi power query is  not available as a COMM addin. This may be because I am using the newest version of excel where power query is built into the programme. It is accessed via the "data tab" under the "get and transform" section?

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.