Connecting and refreshing Excel workbook data

[stvcdms]

Hello,

As part of our effort to build dashboards in Power BI, our first step is providing Excel Workbooks with simple pivot tables for the initial data analysis. Currenly I can't see a way to provide an Excel workbook with the credentials for the Power Query datasources. The source is a DB using DB credentials. We want to make the workbook available for others to download and use locally as we work on providing company wide Power BI dashboards. Is this possible?

[Greg_Deckler]

Not entirely sure I understand your exact use case.

If you use Power Query to create your data sources and when loading, use "Connection only" and "Load to data model" then you Excel should store those credentials. You could create your pivot tables from the data model and then you could upload and share the workbook via OneDrive. Further, you could Connect or Import your Excel file from OneDrive to Power BI, creating reports and dashboards and share the dashboards with your users as well.

Not sure if this is your use case or not so let me know.

[stvcdms]

Thank you for the reply.

Yes! This is my use case, so the issue may be that I am not first using a "connection only" query. I am using a query to pull directly into the data model. When another user gets the workbook, they get prompted for the credentials of the DB. They can't juse refresh the data. 

If I am understanding you correctly, by using a connection only query first, that should store the DB credentials so anyone who gets the workbook can refersh the data?

[stvcdms]

Just to clarify other reading this later. There does not appear to be any way to store credentials in a query. Each user will have to reauthenticate to the data source for the queries to start working again.