Greyed out Edit Query for a table linked to powerpivot model

[masplin]

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

 

[Matt Allington]

Both should work. I suggest you disable the addin then reboot the pc and the. Reenable he addin and see if that fixes it

 

[masplin]

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

 

[masplin]

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.

 

[Matt Allington]

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.

 

[masplin]

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.

 

[Matt Allington]

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