12-02-2018 10:29 PM
12-02-2018 10:29 PM
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"
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.
12-03-2018 06:35 PM
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.
12-04-2018 06:34 PM
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.
12-04-2018 06:51 PMSolution
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.