SOLVED

Power Query does not load ANY data into the data model

Copper Contributor

I can't load any data from power query to the data model.  I'm running 32-bit Excel 365 (v16.0.11029.20045) on Windows 10

 

Every time I try to load data from Power Query to the Data Model I receive the following error message: "We couldn't get data from the Data Model. the connection could not be refreshed because the connection string was invalid. Please remove the connection and try loading the query again." 

 

I've attached the workbook I'm having trouble with.  When I complete the following steps I get the error mentioned above:

Open the file

Data -> Queries & Connections

Right Click on  the query "TransformedStartSales"

Select "Load To.."

Select the radio button "Connection Only"

Check the box "Add the data to the Data Model"

Click OK

The error message appears.

 

One more thing.  When I save the file to my OneDrive folder then complete the steps above there is no error and the data loads to the Data Model.  When I save the file back to C:\\MyDocuments and complete the steps outlined above, the error message returns and I can not load the query to the data model.

 

Thanks for your help.

-Dave J

 

 

 

 

7 Replies
Works without any errors for me.

Thanks for trying it out for me. 

 

I did some more experimentation.  If I save the workbook to any other folder on my computer it works fine just like you say.  When I save it back to my project folder I get the error message.  I guess I could work around this curious behavior its just very strange.

 

 

What does the folder path look like?

Non working folder path:

F:\ShareFile\Personal Folders\Davidj\Plant 1\Projects\038; Highline Excel 2016

 

Working folder paths:

Every other folder on any drive I've tried so far.  Even parent folders of "\\038; Highline Excel 2016" do not give the error.

 

As I write this it sounds so bizarre that I had to go back and verify this behavior.  I open the file from "\\038; Highline Excel 2016" and try to load the query to the data model.  As expected I get the invalid connection string error.

 

Next, I File -> Save As to the "\\Projects" folder and the query loads to the data model just fine.  I don't restart Excel it just simply works.  Finally, I File -> Save As back to "\\038; Highline Excel 2016" and try to load the query to the data model and I get invalid connection string.

best response confirmed by dave_j (Copper Contributor)
Solution

I just found the problem.  The semi-colon in the project folder name was causing  the problem.  When I removed the semi-colon from the folder name I stopped getting the error.

 

Thanks for taking the time to respond and pointing me in the right direction.

 

-Dave J

 

 

My file was stored in SharePoint (in premises) and this recommendation worked.
1 best response

Accepted Solutions
best response confirmed by dave_j (Copper Contributor)
Solution

I just found the problem.  The semi-colon in the project folder name was causing  the problem.  When I removed the semi-colon from the folder name I stopped getting the error.

 

Thanks for taking the time to respond and pointing me in the right direction.

 

-Dave J

 

 

View solution in original post