Background

Queries created with Power Query are expressed using the M language and executed through the M Engine. You build up these queries using a visual editor. If you want to easily share your query so others can easily recreate the report (with a different endpoint or credentials), there are two different options to do so. Below, we explain both.

Copy / Paste

Sample file to download for practicing purposes can be found here (please download it locally, as Book1.xlsx)

  • First, open the file with your source query
  • Show the query editor (by clicking on Data -> Queries & Connections ) and, in the queries pane, right-click the query you want to copy
  • From the dropdown menu, select the Copy option. Please note, that the copy feature is currently not available in Power Query Online instances.
  • The query and its definition will be added to your clipboard

 

  • Authenticate in your destination file with OData source
  • In your destination file you now need to paste the query from your clipboard
  • Go to the queries pane (Data -> Queries & Connections) and right-click on any empty space in it.
  • From the right-click menu, select Paste.

Copy the M code

  • Select the query that you want to share.
  • In the ribbon, select the View tab and then select Advanced Editor.
  • In the Advanced Editor window, select all the code and copy it.
  • Create a blank query
  • Replace the code found in blank query with copied one
  • Click Done and save the changes
  • Click Close & Load to get the data in your Excel sheet

Notes and limitations

Copying and pasting queries between two files doesn't guarantee that all functions and functionality found in the pasted query will work on the destination. Some functionality might only be available in the origin product integration.

If the connection information is different you will need to replace them from the Power Query editor window:

  • Go to View -> Advanced Editor