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
Cannot find the Query Editor in Excel 2106
-
- Posts: 929
- Joined: Sun May 04, 2014 4:01 pm
- Location: Sydney, Australia
Re: Cannot find the Query Editor in Excel 2106
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 Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/
https://exceleratorbi.com.au/power-bi-online-training/
Re: Cannot find the Query Editor in Excel 2106
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.
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.
-
- Posts: 929
- Joined: Sun May 04, 2014 4:01 pm
- Location: Sydney, Australia
Re: Cannot find the Query Editor in Excel 2106
Strange (I guess you know that). Are you able to go into existing connections and browse to the file from there?
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/
https://exceleratorbi.com.au/power-bi-online-training/
Who is online
Users browsing this forum: No registered users and 1 guest