There was a recent discussion of this topic on LinkedIn. When you are in the Power Query window you can’t save the query. If you Close and Load and Excel crashes, you could lose your work. If you have spent some time working on a query this can be frustrating. Here’s how you can capture the query before using Close & Load.

At its heart Power Query is just a set of lines of M code. It is like a programming language, and it is written all in text. Most times you don’t need to know the code, but it is still there in the background.

You can see the code by clicking the Advanced Editor icon on the Home ribbon.

Don’t worry, it’s not that advanced, it just shows you the lines of code – see image below.

This code is what drives the query. You can copy it and paste it into a text file app (like Windows Notepad) before you use Close & Load. See image below.

That way if it crashes you can copy and paste the code back into the Advanced Editor in a Blank Query to return to where you were. See below on how to create a Blank Query.

To be extra careful you might want to save the Notepad file as well.

This technique can also be useful to capture the query at different stages of development. Power Query has no Undo option. You can capture a query at different points and go back to that point if necessary. You can paste the lines of code one under the other in the Notepad file. It would probably be best to add some extra notes between the blocks of code to explain the differences between the queries.

Blank Query

To create a Blank Query click the Data ribbon, click the Get Data drop-down, then click From Other Sources and Blank Query is the last option – see image below.

Using a separate text file can reduce your frustration with Power Query. Hopefully one day there will be save option in Power Query.