Dropdown box greyed out in Table Properties - Power Pivot in Excel 2016

Hi All,

When importing data from the Excel data source to the Power Pivot for Excel, the default view is Table Preview. That's a normal behavior.

When connecting to a database, such as Access or SQL Server, etc. then you should be able to switch between Table Preview and Query Editor. When connection to an Excel workbook you should only have Table Preview.

Sometime in 2016, a bug was introduced that caused the Query Editor view to appear instead of the Table Preview. In Excel data source, the SQL variant used by Power Pivot is NOT really T-SQL and was only intended to be used under the covers by our code. And it was not intended to be editable. Recently, we fixed that bug so that we’re now back to the current behavior which is to show Table Preview as the only option when fetching data from an Excel worksheet using Power Pivot.

For affected customers to easily identify the information here, this thread will be closed. If there are any further queries about using Power Pivot, please start a new thread. We'll focus on the details of the exact scenario to provide support.

Regards,

Felix

Hi All,

We have reported this issue to the related team. Here is the summary:
==============

Issue description:

You can’t switch Query Editor to Table Preview in Excel Power Pivot 2016, and the dropdown box in the Switch to section is greyed out as below:
 

 
Environment:

Excel 2016, version 1610(Build 7466.2038)

Workaround:

Use Access database as Data source in Deferred Channel of Excel (16.0.6965.2115).

More information

We have reported it to the related team for investigation. We will post updates here once avaliable. You can subscribe to this thread for the latest updates.

==============

@ Gill Office applications areinstalled in Cureent Channel by default. To install Office applications in Defferred channel, you can change the settings via Office 365 admin portal > Settings > Apps > Office software download settings >  change to Every 4 months (Deffered channel) as below. Then re-install Office from Office portal.



Also, regarding the crash issue you encounter,  we will continue to assist you with it in this thread. Please provide the following for troubleshooting it:

1.   The result after using a linked table as an alternative approach. For detailed steps, refer to the following article:

Add worksheet data to a Data Model using a linked table

2.  Does the issue occurs to the specific workbook? Also, copy the contents in the affected workbook to a new workbook and see the outcome.

3.    When did you get this issue? Can you share the detailed steps and related screenshots with us?

4.    A screenshot about your Excel version.

Regards,

Spike