After an upgrade to Excel 2016 I found a task I perform regularly had been changed. I build workbooks with embedded stored procedure calls, my users Refresh their data when needed. In place of manually building the connection is the incredibly helpful Power Query wizard. While the wizard goes for simplicity I find other additional options I might need inside the editor once the wizard has done its magic.
To get started just use the wizard for getting data from SQL Server. Starting on the Data tab go to Get Data > From Database > From SQL Server Database.
The next window asks for some basic connection information. I provide a database and SQL statement here but this can be done once a connection has been made.
When you click Run a preview of the data is displayed if you have a connection with that SQL Server already. Otherwise you’re going to see a prompt for some form of credentials. Using Windows Authentication is the most ideal but in the real world that might not be an option. If you have to use the SQL Server credentials just know you may have to share those credentials with the other users that plan on using this query.
Once you successfully have data in the Power Query Editor if you still want to make adjustments to your connection use the Data Source Settings window. I also recommend updating the Name of your Query to something meaningful. When you start working with multiple queries having them named makes them more manageable. Hit Close & Load when your updates are complete.
The data loads in the workbook and there is a Queries & Connections section to manage your new Query. This would be the point I would save the Excel and give it to someone else to just refresh the data. When doing this now the user gets an error or in other cases gets a prompt for server credentials. This does me no good.
In my research I learned about SharePoint services for sharing data connections using a trusted connection library. This options sounds great if it’s available to you in your Office 365 setup. I’m not one of the fortunate. I looked for a way to share a Query and ran across the Export Connection File option.
When you select this option a File Save window launches prompting you to save an Office Database Connection file. This defaults to your My Data Sources folder which is the default location of your local Excel data sources. The main purpose is to give users a way to share these queries between workbooks locally and if the query is updated in one place every other location the query automatically updates.
After the export I use OneDrive to share the connection file with another Office 365 user. You could e-mail the file to someone and have that person download it, OneDrive isn’t necessary. This user downloads the file to his local My Data Sources folder, opens a brand new Excel workbook, and opens his Existing Connections to see this file listed.
Select the connection file and click Open. From here you just need to drop the data into Excel. Good luck out there.