Power Query won't execute queries

[mikeymay]

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 -

When I debug this it is on the following line of 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

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'

The query executes but the next query in the 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

 

[mikeymay]

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!!!