Exporting All M Code From Power Query In Excel 2013

UPDATE – there’s now a better way to do this using copy/paste. See
https://blog.crossjoin.co.uk/2017/02/25/exporting-your-queries-m-code-from-power-query-and-power-bi-using-copypaste/

Here’s a tip that I found out about on the Power Query Technet forum that I thought was worth repeating. If you ever need a quick way of exporting all the M code for all of the queries in an Excel 2013 workbook, just open the Power Query window and click the Send a Frown button shown here:

image

Then, when the Send Feedback dialog appears, make sure Include Formulas is ticked then click OK:

image

When you do that, you’ll get an email created for you that contains a whole lot of debugging information, plus all of the M code for your queries:

image

Obviously, don’t send this email to Microsoft!

It’s quite easy to see the individual queries. You then need to go to your new workbook, create a new query by selecting the Blank Query option under Other Sources, and then open the Advanced Editor window and paste the code for each query in. However, when you do that you will need to modify the code a bit. There are three pieces of code you will need to delete:

  • At the beginning of the M code, where it says
    section Section1;
  • At the beginning of each query, a piece of code that contains the original name of the query:
    shared MyOriginalQueryName =
  • At the very end, a semi-colon

Of course in Excel 2016 we’ll have much better options for copying, pasting and moving queries in VBA, but until then we’ll have to live with hacks like this.

9 thoughts on “Exporting All M Code From Power Query In Excel 2013

  1. Hi Chris,
    it seems that Microsoft has changed the “Send a Frown” dialog.
    It has just two checkboxes:
    – Include screensht
    – Include email address
    and by pressing “Submit” no email is generated, instead the message is automatically sent with the screenshot.

    Did you find any workaround in the meantime?

    • Are you doing this in the browser on in Power BI Desktop? I still see an email with the code generated when I do this in Power BI Desktop using the latest release.

      • Hi Chris, thanks for your reply.
        I am actually trying to do it in Excel
        Your article focuses on Excel 2013 and I thought the dialog screen would be similar in Excel 2016, but
        I actually get a totally different screen (http://imgur.com/pi88BHl)

        I can also confirm it works in the current version of Power BI Desktop.

        Thanks as usual for your great work,
        Andrea Catalucci

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s