Error in Excel when executing a non-existing query

[christiane.latte]

Hei,

I constantly get an error message because excel is trying to execute a query that i obviously not there. Or is it?

This the error message I get.

I'd love to delete this stupid query but can't find it the query overview. For an awkward reason it is still loaded to the data model. See here


But here I can't delete it...


So what do I do now?

Kr,
Christiane

 

[Ken Puls]

Hi Christiane,

You need to delete this one from Power Query, not Power Pivot. Since you're on Excel 2016, you'll need to go to the Data tab. Depending on the version of Excel 2016 you have, you'll find one of the following two buttons:
-Show Queries
-Queries & Connections

Whichever it is, when you click it, the Queries pane should fly out from the right side. Find the query in that list.

You have two options here:
1) Right click --> Load To --> uncheck Load to Data Model

This option will revert the query to a Connection Only query which will not get called. Essentially, it allows you to remove it from the Power Pivot Data Model, but keeps the query around in case you do need it in future. This is probably the safest route, unless you know for sure you do not need it.

2) Right click --> Delete

This option will delete the Power Pivot table, and permanently delete the query as well.

HTH,

 

[christiane.latte]

the Queries pane should fly out from the right side. Find the query in that list.

That is my problem, I can not find it in this list of queries. I looked many times, very carefully. It is not there.

 

[christiane.latte]

No one?

 

[AliGW]

How can we find it for you without the workbook?

 

[Ken Puls]

Hi Christine, like Ali said, it's going to be very difficult to find without access to the workbook.

I'm curious about your version of Excel 2016. Is the first button on your Data tab called Get Data or is it something else? The reason I'm asking is because the method to get in to the workbook connections has changed in Office 365 updates. My next thing to hunt here would be to look into the Connections area to see if I can locate a connection and delete it. (This pane was harmonized into Queries & Connections in Office 365.)

Failing that, I'd be hunting this using VBA to see if I can find it and remove it. Unfortunately, due to the time involved in this, I'd have to get one of my team on it, and that - unfortunately - is a chargeable service. (If you're interested, ping me via PM and we can organize it.)

Let me know more details about your version and we'll see if we can avoid that last bit.

 

[christiane.latte]

Ah..I see. Thanks for the advises. The file I am refererring to is a 22 MB xlsx and our complete reporting. All transactions, all business internals. Something confidential I cannot unfortunately share somewhere online.