Power Query won't execute queries

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,477
I have a file that imports multiple datasets in the the data model via PQ and reports on people at region and territory levels.

I have recently had to implement some region and territory name changes due to an internal restructure and now the execution of queries to pull in the data has stopped working

When I revert back to the old structure of regions, the execution of the query performs as required but when the new structure is implemented I am getting errors saying -
1599232461510.png


When I debug this it is on the following line of code
VBA Code:
ActiveWorkbook.Connections("Query - SourceFileName").Refresh
which is getting a default folder location and the query is showing


I am able to edit the query and the 3 steps seem to work
1599233080376.png


I am then able to close and load but I still get the same error message about the load to the data model failed.

If I click the refresh button for the query in the Queries & connection window, the error message then says Download Failed and I am able to select where to load to. If I select 'ONly Create Connection'
1599233217673.png


The query executes but the next query in the code
VBA Code:
ActiveWorkbook.Connections("Query - CRM").Refresh
Which uses the SourceFileName query to establish the file to import, I get a Run Time Error 9 Subscript out of range

Again I can use the Load To option which then execute the import but the pivot tables that are connected to this data have lost some of the fields that were being used.

I appreciate the above is quite detailed but I don;t understand why some changes in region names can cause the above.


TIA
 

Attachments

  • 1599232951030.png
    1599232951030.png
    221.2 KB · Views: 3
  • 1599233059355.png
    1599233059355.png
    188.3 KB · Views: 3
Last edited by a moderator:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,477
Update - I have asked someone who isn't on a O365 subscription to try importing the data via PQ and they have no issue at all!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,015
Messages
5,575,560
Members
412,676
Latest member
Davejf81
Top