Greyed out Edit Query for a table linked to powerpivot model

masplin

Active Member
Joined
May 10, 2010
Messages
413
In the past when I want to extract my data from the data model as a table, instead of a pivottable, I use a query like this "evaluate calculatetable( summarize( vehicle,vehicle[VRM],..... " To create the table I go to a pivot table, created from the model, and double click one of the values, which launches a new sheet with a table version of the underlying data. I would then right click and go down to "Table" and then click edit query to replace the default query with my one above. This has always worked for numerous models, but a new one i created today this option is greyed out?

The other way i think to do this is go to data /existing connections, tables , chose model and open and should be able to chose "table", but again this is greyed out

Anyone got any clues how to unlock this?

appreciate any advice

Mike
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Both should work. I suggest you disable the addin then reboot the pc and the. Reenable he addin and see if that fixes it
 
Upvote 0
Unfortunately not. Disabled all com addin, reboot and still the same?

I have some old workbooks where the options are enabled just not this work book
 
Upvote 0
Just got to work opened a new workbook, loaded a single csv table via power query. Added a pivot table with simple count, double clicked value and again he resulting table the edit options are greyed out. So this is a different computer and fresh work book and data.
 
Upvote 0
I’m not in front of my pc. But my recollection is that double click pivot may not work for a power pivot model, but it does if you are using analyse in Excel for Power BI service. But I thought the “existing connections\tables\data model approach should work. From memory.
 
Upvote 0
Hmmm I'm sure I've done this before using powerpivot. One i tested yesterday that works is linked by BI publisher which is similar to Analyze in Excel.

Anyway I just checked and the existing connection route has now allowed me to create a table and now the "edit DAX" option is available on the pc at work. Will recheck at home to see if disabling the addins fixed that

Thanks a lot as at least have a method that will work.
 
Upvote 0
I am with you. I seem to remember that I could do this too, then one day it didn't work. That is how I new that it did work with Analyze in Excel and also with the Existing connections approach - I had to use these as a work around when doing a demo a little while back
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top