If you don't have Power Query installed you'll get the following error message:
Microsoft.Mashup.OleDb.1 provider is not registered on the local machine
(Had anyone ever tried marking this message as "unhelpful"?)
In the Data ribbon,
If you go to Existing Connections -> Select a Connection or Table -> Mark the connection and right click it -> Edit Connection Properties -> Definition Tab
you will see that the connection string starts with:
Provider=Microsoft.Mashup.OleDb.1
If you use only Power Pivot and you try to link to a connection, then the connection string looks like this:
Provider=SQLOLEDB.1;Integrated Security=SSPI;
Why is it mixed? Why are there two types of connection strings?
It depends on who started the Power Pivot query and when. There is also a difference if you have Power Query Installed on your machine or not. Unfortunately, if you installed Power Query and then upgraded office you might need to install Power Query again (or, at least, Repair it).
As far as I'm concerned this is the main problem with Excel as a Reporting tool:
Excel is amazing. It could do many things, use many languages (DAX & MDX) but if you can't share it easily between people since it is flavor-dependent (are you using 2013 or 2010 or 2016 or Office 365? do you have Power Query installed or not?) then it causes huge problems. Ribbons disappearing, Queries have to be rewritten, connection strings have to be rewritten... Version control shouldn't be such a huge issue, IMHO.
No comments:
Post a Comment