You can share external data connections between Excel users by using an Office Data Connection (ODC) file. ODC files are designed to be portable and easy to update. Use the following approach to share external data sources with data shaped by Power Query.
Note
Although ODC files remain supported, many organizations use SharePoint, OneDrive, Power BI dataflows, or other centralized data sources to share Power Query solutions.
In this example, you share a workbook with data imported from a data workbook. But this process applies to almost all external data sources that Excel can import and connect to. The only exception is that you can’t save connection information to an ODC file for an Excel table or named range in the same workbook from which you imported it (by using Data > From Table/Range).
Important
If you experience issues with authentication or privacy, see Manage data source settings and permissions (Power Query) and Set privacy levels (Power Query).
Step 1: Import data and save connection and query information to the ODC file
To make the data easy to share, one person completes the following steps:
- Select Data > Get Data > From File > From Excel Workbook.
- In the Import Data dialog box, locate and select the workbook containing the data, and then select Import.
- In the Navigator dialog box, select a table, and then select Transform Data.
- In the Power Query Editor, shape the data the way you want, and then select Home > Close & Load.
- In the Excel worksheet containing the query data, select a cell in the table, select Data, select the arrow next to Refresh All, select Connection Properties, select the Definition tab, and then at the bottom of the dialog box, select Export Connection File.
- In the File Save dialog box, enter a file name, select Save, and then select OK.
- Ensure you save the file to a trusted location that others can access, such as a network folder, SharePoint library, or OneDrive folder. For more information about trusted data sources, see Trusted documents, View my privacy options in the Microsoft Office Trust Center, and Add, remove, or change a trusted location.
- Save the workbook.
Result
The ODC file contains all the connection information about the external data source and the steps and formulas of the Power Query operations. If you want, open the ODC file in Notepad to examine the XML format used.
Step 2: Open and use the saved ODC file
To access the shared data, another person completes the following steps:
Locate the ODC file, and then open it.
Excel opens the ODC file into a new Excel workbook.
If you get a message about trusting the data source, select Enable.
In the Import Data dialog box, decide how you want to import the data, and then select OK. For more information about using this dialog box, select the question mark (?).
To confirm everything is working properly, update the workbook data source, and then refresh all the data in this workbook by pressing Ctrl + Alt + F5.
Save the workbook.
Result
The other person can now share an external data source with its data shaped by Power Query.