A great way to share data between Excel users is to use an Office Data Connection (ODC) file. They are designed to be portable and easy to update. We recommend the following approach to sharing external data sources with data shaped by Power Query.
In this example, we 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. 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 have imported it (by using Data > From Table/Range).
Important If you experience issues with authentication or privacy, see Manage data source settings and permissions and Set privacy levels.
To make data easy to share, one person does the following:
-
Select Data > Get Data > From File > From Workbook.
-
In the Import Data dialog box, locate and select the workbook containing the data, and then select Open.
-
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.
-
Make sure you save the file to a trusted data source in a location another person can access, such as a network folder. For more information about trusted data sources, see Trusted documents, View my privacy options in the 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.
To access the shared data, another person does the following:
-
Using File Explorer, locate the ODC file created in the previous section, and then double-click 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.
