Exporting Your Queries’ M Code From Power Query And Power BI Using Copy/Paste

Two years ago I blogged about a method to export all the M code for all of your queries in Power Query using the Send A Frown button – useful if you need the code for documentation purposes. This trick doesn’t work with Power BI Desktop, unfortunately, but the good news is that there’s a better way to do this now in Power Query and Power BI Desktop using copy/paste. It’s pretty simple really: when you copy a query from the Power Query or Power BI Desktop Query Editor you can not only paste the query to another Query Editor (pasting from Power Query to Power BI and vice versa works too) but you can also paste the query to a text editor like Notepad and get the M code for the query. What’s more, you can also select more than one query in the Query Editor and when you paste you get all the code for all of the selected queries:

image

Remember that because the properties of each step in a query become comments in your M code, they get copied too.

Thanks to VossF for telling me about this on this thread.

14 responses

  1. Pingback: Exporting All M Code From Power Query In Excel 2013 – Chris Webb's BI Blog

  2. What is the best way to replicate an entire data model (containing Power Queries, Power Pivot tables, measures, linked date tables, Pivot Tables, cube formulas, etc.)? I recently created a data model for one company using one data base. I then needed to duplicate the exact same thing for another company that used the exact same structure (same table and field names) in another data base. I was hoping all I needed to do was change the name of the data base in the Connection of the Excel file. My attempt was not successful.

  3. I was thrilled when I received your reply on the TechNet forum knowing I passed a useful tip on to someone whose work I greatly admire but opening your site and reading the headline of your latest post and being thanked for the tip just filled me with a sincere feeling of belonging to the thriving Power BI community where people like you and many others openly share your knowledge. Thank you.

  4. Really interesting.

    It would make a lot of sense to be able to do the same for the DAX part where all custom columns and measures are easily copy/pastable, both for documentation and reproducibility purposes.

    I hope MS gets there eventually.

  5. Pingback: Extracting Power Queries | Query Power 📊

  6. Pingback: #Excel Super Links #8 – shared by David Hager | Excel For You

  7. Pingback: Thoughts On The New Power Query Source In SSIS « Chris Webb's BI Blog

  8. Pingback: Thoughts On The New Power Query Source In SSIS - Beyond Group

  9. This tip just saved me a bunch of time! Thanks…

    What would really be helpful is if you could open the Advanced Editor and IMPORT the same set of Queries. You would have to put some sort of key text inbetween each query so the editor would know to parse them out into individual queries.

    Something like:
    QueryName: TableQuery1
    let
    Source = bla, bla, bla
    in
    Source

    QueryName: DimQuery1
    let
    Source = bla, bla, bla
    in
    Source

    QueryName: fxQuery1

    (MyFIle as binary) => as table
    let
    Source = bla, bla, bla
    in
    Source

    When imported would generate three individual queries (Including functions). Maybe a different button on the ribbon for “Import Queries”. This would be very helpful when stuck with rebuilding a data model due to corruption or other issues. Export out queries, make any code changes needed, import into fresh workbook or PBI file, rebuild relationships, fix up Calendar table and you are off to the races.

  10. I accidentally discovered this myself and at first it was giving me pretty well much what I was looking for but then later that same day, everytime I copied the Queries and pasted to Notepad, I was not getting the properties included.

    Initially what I got from the copy was a single line (minified) xml file but now what I get is neatly formatted text but without the properties

    I have no idea why I can’t get the xml file as originally received but would like to be able to do so. Do yo have any advice?

  11. Ok – After pulling my hair out thinking I was truly crazy I have now discovered (accidentally again) what the answer is.

    If you copy the queries and paste directly into Notepad for instance, you will get the neatly formatted M Code for each query but with no properties! The same result occurred if you paste into one of the online xml beautifier tools.

    If however you past the copied queries into an excel worksheet cell, you get the single line minified version of the XML file which includes all the properties. You do however need to be mindful of the fact that this may trim some of the code if you have copied a number of queries due to excel’s limitation of how much text you can paste into a cell.

    Now this can be very useful in producing some form of documentation of your Excel Power Queries

Leave a Reply to Kristan Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: