Cannot find the Query Editor in Excel 2106

Anything related to PowerPivot and DAX Formuale
IanR
Posts: 2
Joined: Wed Aug 09, 2017 11:30 pm

Cannot find the Query Editor in Excel 2106

Postby IanR » Thu Aug 10, 2017 1:33 am

Hi,

I cannot find the Query Editor in Excel 2106.
I have a model that is connected to an Access database. I have added a new field to the Access query that I am connecting to but this field does not appear when I refresh from Excel. Thinking that Power Pivot will work in a similar way to Power BI Desktop, where I have to select the columns that I am going to use in the query editor, I thought I would try that. Unfortunately, after a lot of searching I cannot find a way of starting the query editor.

Is it not available for connections to Access?
Is there another way to force new fields from Access into the PowerPivot model? It seems a bit extreme to have to create a new Excel file and a new model with all its measures and calculated columns just to add a new field.

I am using Excel 2016 version 1706. if it's relevant I imported the data directly into PowerPivot rather than via a table in Excel.

Thanks
Ian

MattAllington
Posts: 844
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Cannot find the Query Editor in Excel 2106

Postby MattAllington » Thu Aug 10, 2017 7:21 am

If you import directly, then you should be able to bring up the import wizard by first selecting the table in Power Pivot, then select table properties. You can add the new column from there
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

IanR
Posts: 2
Joined: Wed Aug 09, 2017 11:30 pm

Re: Cannot find the Query Editor in Excel 2106

Postby IanR » Fri Aug 11, 2017 1:34 am

Hi Matt,

Thanks for your reply.
When I open the table properties window the 'Switch To: Query Editor' option is disabled. At the bottom is a flash of yellow which contains the first line of an error message. Copying that to NotePad I managed to extract the message below (which has a duplicated first two sentences). This tells me that the path to my Access database is not valid. I copied the path, which includes the file name, into Windows Explorer and it opened the database successfully. The path is therefore valid. I'm stumped.

Thanks
Ian

Error message from Table properties:

Cannot connect to the data source for table "Report_PrevRepairs". Make sure that Data Source is available. If you contact Microsoft support about this error, provide the following message:
Cannot connect to the data source for table "Report_PrevRepairs". Make sure that Data Source is available. If you contact Microsoft support about this error, provide the following message:
Failed to connect to the server. Reason: 'T:\Repairs Database\Customer_Services_BE_Live.accdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

MattAllington
Posts: 844
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Cannot find the Query Editor in Excel 2106

Postby MattAllington » Fri Aug 11, 2017 6:01 am

Strange (I guess you know that). Are you able to go into existing connections and browse to the file from there?
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests