I have some data which I have modeled cleaned in a master excel sheet, and then export the final data to a text file. This is then imported into various excel sheets using Power Query to be loaded in pivot tables and used to generate graphs for reports.
Recently, I think that somewhere in the background, my primary query has somehow been duplicated and now I cannot refresh data. This has happened in ALL of my sheets somehow.
Let's call my main query Current_data. Well I have recently noticed that refresh all queries never completes and when I exit the file, I get the dialogue that Current_data1 could not refresh. The issue being that I cannot find Current_data1 in the queries pane. If I click on "Change data source" in the pivot table options, I can see Current_data1 in the list of connections, and all of my pivot tables are linked to this and not Current_data. But the 2 data sources are somehow linked through the same query. I.e. if I delete Current_data from the queries pane, all of my pivot tables unlink from data.
I have noticed that the Current_data query in the query pane shows as connection only, meaning the Current_data1 query which all of my pivot tables are linked to is not accessible to edit. Is this a glitch, or do I have a hope if being able to find the invisible query?
The only fix I can see is to change the data source on all of my pivot tables by hand (which will create a new Current_data1/2/3 increment, which I am guessing will bloat my sheets beyond being useful. Or create a new query from the data source, recreate all graphs - enormous time loss.
Does this mean anything to anyone? Any idea of how to fix before I start working silly hours to stay with deadlines?
Is it possible you loaded Current_data1 to the Power Pivot Data Model?
Thanks for your reply.
There is nothing in the PP data model.
I do have an update though. It seems that it has only affected 2 of my sheets rather than all. They all have the duplicate query, which suggests it was there during setup as the sheets are copies from a starter template which contains the dupe.
They work fine, so I think what has happened is that I have loaded my original query as connection only in the beginning, then the duplicate is created as a reference to the original query when I loaded it to pivot chart report. So the duplicate itself is not the issue.
However, I just remembered that the other day, I accidentally copied a tab from the first affected sheet to the other affected sheet, which created a bunch of duplicates, which I then deleted. I can only assume that something I deleted resulted in a link being lost between the original query and the original duplicate (created in the template) for those 2 sheets.
Still something of a mystery as to exactly what happened, but fortunately it's more of an annoyance than a show-stopper on closer inspection.
