SOLVED
Home

Power Query does not load ANY data into the data model

%3CLINGO-SUB%20id%3D%22lingo-sub-293953%22%20slang%3D%22en-US%22%3EPower%20Query%20does%20not%20load%20ANY%20data%20into%20the%20data%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293953%22%20slang%3D%22en-US%22%3E%3CP%3EI%20can't%20load%20any%20data%20from%20power%20query%20to%20the%20data%20model.%26nbsp%3B%20I'm%20running%2032-bit%20Excel%20365%20(v16.0.11029.20045)%20on%20Windows%2010%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEvery%20time%20I%20try%20to%20load%20data%20from%20Power%20Query%20to%20the%20Data%20Model%20I%26nbsp%3Breceive%20the%20following%20error%20message%3A%20%22We%20couldn't%20get%20data%20from%20the%20Data%20Model.%20the%20connection%20could%20not%20be%20refreshed%20because%20the%20connection%20string%20was%20invalid.%20Please%20remove%20the%20connection%20and%20try%20loading%20the%20query%20again.%22%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20the%20workbook%20I'm%20having%20trouble%20with.%26nbsp%3B%20When%20I%20complete%20the%20following%20steps%20I%20get%20the%20error%20mentioned%20above%3A%3C%2FP%3E%3CP%3EOpen%20the%20file%3C%2FP%3E%3CP%3EData%20-%26gt%3B%20Queries%20%26amp%3B%20Connections%3C%2FP%3E%3CP%3ERight%20Click%20on%26nbsp%3B%20the%20query%20%22TransformedStartSales%22%3C%2FP%3E%3CP%3ESelect%20%22Load%20To..%22%3C%2FP%3E%3CP%3ESelect%20the%20radio%20button%20%22Connection%20Only%22%3C%2FP%3E%3CP%3ECheck%20the%20box%20%22Add%20the%20data%20to%20the%20Data%20Model%22%3C%2FP%3E%3CP%3EClick%20OK%3C%2FP%3E%3CP%3EThe%20error%20message%20appears.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20more%20thing.%26nbsp%3B%20When%20I%20save%20the%20file%20to%20my%20OneDrive%20folder%20then%20complete%20the%20steps%20above%20there%20is%20no%20error%20and%20the%20data%20loads%20to%20the%20Data%20Model.%26nbsp%3B%20When%20I%20save%20the%20file%20back%20to%20C%3A%5C%5CMyDocuments%20and%20complete%20the%20steps%20outlined%20above%2C%20the%20error%20message%20returns%20and%20I%20can%20not%20load%20the%20query%20to%20the%20data%20model.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help.%3C%2FP%3E%3CP%3E-Dave%20J%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-293953%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364241%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20does%20not%20load%20ANY%20data%20into%20the%20data%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364241%22%20slang%3D%22en-US%22%3EMy%20file%20was%20stored%20in%20SharePoint%20(in%20premises)%20and%20this%20recommendation%20worked.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-295189%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20does%20not%20load%20ANY%20data%20into%20the%20data%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-295189%22%20slang%3D%22en-US%22%3EOutstanding!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-295064%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20does%20not%20load%20ANY%20data%20into%20the%20data%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-295064%22%20slang%3D%22en-US%22%3E%3CP%3EI%20just%20found%20the%20problem.%26nbsp%3B%20The%20semi-colon%20in%20the%20project%20folder%20name%20was%20causing%26nbsp%3B%20the%20problem.%26nbsp%3B%20When%20I%20removed%20the%20semi-colon%20from%20the%20folder%20name%20I%20stopped%20getting%20the%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20taking%20the%20time%20to%20respond%20and%20pointing%20me%20in%20the%20right%20direction.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-Dave%20J%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-295061%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20does%20not%20load%20ANY%20data%20into%20the%20data%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-295061%22%20slang%3D%22en-US%22%3E%3CP%3ENon%20working%20folder%20path%3A%3C%2FP%3E%3CP%3E%3CSPAN%3EF%3A%5CShareFile%5CPersonal%20Folders%5CDavidj%5CPlant%201%5CProjects%5C038%3B%20Highline%20Excel%202016%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWorking%20folder%20paths%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EEvery%20other%20folder%20on%20any%20drive%20I've%20tried%20so%20far.%26nbsp%3B%20Even%20parent%20folders%20of%20%22%5C%5C038%3B%20Highline%20Excel%202016%22%20do%20not%20give%20the%20error.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20I%20write%20this%20it%20sounds%20so%20bizarre%20that%20I%20had%20to%20go%20back%20and%20verify%20this%20behavior.%26nbsp%3B%20I%20open%20the%20file%20from%20%22%5C%5C038%3B%20Highline%20Excel%202016%22%20and%20try%20to%20load%20the%20query%20to%20the%20data%20model.%26nbsp%3B%20As%20expected%20I%20get%20the%20invalid%20connection%20string%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20I%20File%20-%26gt%3B%20Save%20As%20to%20the%20%22%5C%5CProjects%22%20folder%20and%20the%20query%20loads%20to%20the%20data%20model%20just%20fine.%26nbsp%3B%20I%20don't%26nbsp%3Brestart%20Excel%20it%20just%20simply%20works.%26nbsp%3B%20Finally%2C%20I%20File%20-%26gt%3B%20Save%20As%20back%20to%20%22%5C%5C038%3B%20Highline%20Excel%202016%22%20and%20try%20to%20load%20the%20query%20to%20the%20data%20model%20and%20I%20get%20invalid%20connection%20string.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-294530%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20does%20not%20load%20ANY%20data%20into%20the%20data%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-294530%22%20slang%3D%22en-US%22%3EWhat%20does%20the%20folder%20path%20look%20like%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-294453%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20does%20not%20load%20ANY%20data%20into%20the%20data%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-294453%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20trying%20it%20out%20for%20me.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20some%20more%20experimentation.%26nbsp%3B%20If%20I%20save%20the%20workbook%20to%20any%20other%20folder%20on%20my%20computer%20it%20works%20fine%20just%20like%20you%20say.%26nbsp%3B%20When%20I%20save%20it%20back%20to%20my%20project%20folder%20I%20get%20the%20error%20message.%26nbsp%3B%20I%20guess%20I%20could%20work%20around%20this%20curious%20behavior%20its%20just%20very%20strange.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-294011%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20does%20not%20load%20ANY%20data%20into%20the%20data%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-294011%22%20slang%3D%22en-US%22%3EWorks%20without%20any%20errors%20for%20me.%3C%2FLINGO-BODY%3E
dave_j
New 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.

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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies