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:
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.
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.
You should create parameters for the database connection information and then save your file as a Power BI template: https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/
Dear Chris, great post,
Any ideas what to do if I have the same tables that in the old situation sit in a Synapse SQL database and are moved to a Azure Databricks database?
I do work with Parameters, but the thing is that I need a different PQ function (Source = Sql.Database() needs to become Source = Databricks.Catalogs()…)
I can copy all queries to notepad, but as soon as I do a Find + Replace there, I can’t seem to paste back to PQ anymore.
Any thoughts?
I guess the only option will be to paste back into the Advanced Editor or look into scripting the changes using Tabular Editor
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.
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.
You can do this with Tabular Editor: https://www.managility.co/bulk-measure-operations-in-power-bi/
Great article. However, when I paste into notepad the formatting is lost. Is there a way to retain the format?
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.
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?
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