Get & Transform credentials prompt

I have the same issue as the gentleman from Ratheon. I have built a file that successfully queries a client's Sql database, and now want to give that file to one of their accounting folks to refresh...

BI & Data Analytics

Get & Transform Data

Daniel Levite

Copper Contributor

Nov 30, 2017

In case you do not know, I have learned something unfortunate about Power Query.

When you create a data connection to a database, the tool stores the credentials (see Data Sources Settings dialog) in a file called User.zip

This file is stored in the following locations on the user’s machine

(2013) C:\Users\1037591\AppData\Local\Microsoft\Power Query

(2016) %LocalAppData%\Microsoft\Office\16.0\PowerQuery for Excel 2016

The credentials are stored in this file using an encryption key defined by the author and the domain he is on.

This means it will be impossible to author a power query connection to a database and deploy the connection without also telling the other users the credentials to connect.

Nick Pallotta

Copper Contributor

Feb 27, 2018

Thanks for your research, this is very sad. For my Project Server use, I can build the same exact connection by hand using the data tab and connection properties. But it's time consuming. Much easier to paste my SQL code into the Get and Transform editor. I'll dig a little more. Queries should be shareable.

Forum Discussion

Get & Transform credentials prompt

Resources