UPDATE – there’s now a better way to do this using copy/paste. See
Exporting Your Queries’ M Code From Power Query And Power BI Using Copy/Paste
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:
Then, when the Send Feedback dialog appears, make sure Include Formulas is ticked then click OK:
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:
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.
Brilliant! Funny how so many ideas are born from situations where the original purpose was so different.
Very cool. Thanks Chris!
Hi Chris,
A few months ago Imke Feldman gave us PQ management studio which basically is based on “send frown” technique.
You can see her file here (technet gallery).
https://gallery.technet.microsoft.com/Power-Query-Management-ec002f62
Regards
Thanks Bill, I had missed that one
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