Using PowerQuery I have created some Excel workbooks that pull data from another workbook. The purpose is to pull out specific pieces of information that some users need to see and keep the other data confidential.
All of these spreadsheets are stored on SharePoint because they need to be accessed by many users. However, when users attempt to open the workbooks that are using the PowerQuery to pull the information, they get the following error: "Initialization of the data source failed. Check the database server or contact your database administrator..."
My SharePoint administrator is telling me that I need for all of the users to have PowerQuery installed on their machines and they will all need to be given rights to the original workbook which holds the confidential information. This seems to defeat the purpose of using PowerQuery if that is actually the case. Has anyone else encountered this and is there another solution than what the SharePoint administrator has proposed?
Thank you for your assistance.
If you've 'Closed & Loaded' that query to a table in the workbook then any user should be able to see the data. If they get the error/warning they shluld be able to just close it.
However, they can only refresh the query if they have both Power Query installed and SharePoint permissions to the source data.
Mynda
Hi PS-C21H,
Please provide a link to all forums where you have posted this question. All the people that will try to provide an answer to your problem should be aware of eachother.
See more about the downsides of crossposting here: Crossposting
Thanks for understanding.
Catalin
First Catalin, I apologize for not following the posting etiquette. Thank you for educating me on that. I posted to two other sites and neither game me a helpful response.
I hope it is appropriate to add this reply/question here since it is related to the same topic.
Mynda, I do appreciate your feedback. My SharePoint administrator and I took a little different approach. He created a service account on SharePoint and we gave that account rights to the other workbooks that are my data source instead of giving access to the many individual people. This seems to be working (now that they have PowerQuery installed), but there is one thing that isn't working and I am not sure if it is something I am missing or if it is just something that must be done this way. Everyone who opens the workbooks that are pulling from the original Excel files on SharePoint have to enter the service account's username and password. Is there not a place I can set that so the end users do not have to bother with entering that information? I have added the service account to the following places in the files myself...
DATA tab > Connections > Properties > Definition tab > Excel Services: Authentication Settings > Use a stored account and I have put the service account name there. (Wasn't sure if I should, but it seemed to make sense.) However, that didn't seem to do anything.
So then I found on the POWER QUERY tab > Data Source Settings > I picked the SharePoint connection > Edit > Credentials Type: Windows > Edit > Use alternate credentials - and I put the service account username and password there. However, again the users are still being prompted to add that information again when they open the files.
Are there any other places to store the username and password that will keep users from having to put in this information? I greatly appreciate your assistance!!
When you create a new query from Online Services-From SharePoint Online List, In the Access SharePoint dialog box that appears next, you have 3 credentials options:
Select Anonymous if the SharePoint Server does not require any credentials.
Select Windows if the SharePoint Server requires your Windows credentials.
Select Organizational account if the SharePoint Server requires organizational account credentials.
Keep in mind that the credentials are stored on your computer, you will not be prompted again for credentials.
If the file is going to be used by other users on other computers, for the first use they will be asked to enter their credentials. After that, they will not be asked again for credentials. In case you expected that no other users will be prompted again to enter credentials, it's wrong, because it is a security problem. What if the file is used by other people, maybe your competitors... You certainly don't want them to update the data without credentials.
Hope it helps.
Catalin
I created a workbook with power query and power pivot table and charts. I have another user who tries to open the file, click on a slicer and gets the same error as mentioned earlier("Initialization of the data source failed. Check the database server or contact your database administrator...")
I've tried researching this, but haven't had any luck. Any help you can provide would be appreciated.
thanks,
Susan
Hi Susan,
This isn't a Power Query issue, it's a Power Pivot issue. If the user wants to interact with the Slicers then they require Power Pivot, and the same version of Power Pivot as you. More on the error here: https://support.office.com/en-us/article/Power-Pivot-error-Initialization-of-the-data-source-failed-55d6b559-3124-4583-85fb-deb2fa1c098b
Mynda
