Power Query (Excel 2010) - I shared workbook and now data won't refresh for other user

[imnie]

Hi all,

I've used Power Query to build a workbook that contains queries pulling in from two external databases - MySQL and Teradata, and then using Power Query I do some manipulation for the final output. I'm trying to understand why when I shared this workbook (sent via e-mail as an .xls) the recipient can open, but not refresh the data. She gets the error "Initialization of the Data Source Failed".

Questions are: 1) Do I need to forward each .odc file created by Power Query? and 2) where do other users get the opportunity to input their credentials to query against the databases (these are unique to each user)?

Thanks for any insight / suggestions.

Crystal

 

[Matt Allington]

Database credentials are saved in thhe workbook. Here are the 2016 details. https://support.office.com/en-us/ar...er-Query-9f24a631-f7eb-4729-88dd-6a4921380ca9

2013 should be similar. I don't expect you need to send the odc file, but I have never tested it

 

[RoryA]

Does it still work if you send as .xls rather than newer file format?

 

[Matt Allington]

I don't see why not. I must admit I missed the 2010 reference, but power query is almost identical, so I don't see an issue.

 

[RoryA]

I wasn't sure whether the old BIFF8 format could store the PQ information, given that it predates PQ's existence.

 

[Matt Allington]

Well it is so long since I sent an xls, I have forgotten what versions of Excel use it. I am pretty sure you will have to save the file as native 2010 format for it to work (as you have suggested). If that is xlsx, then I agree that xls will not likely work.

 

[RoryA]

I actually just did a quick test and it seemed to work even with an xls file, though it was a simple extract from another workbook - no security involved. I don't know if that could be an issue.