Excel Power Query - How to save a code for future use?

[anna99]

Hi all,

I got code from Advanced Editor in excel power query, how can i save this code as a button (same like we did for macro/vba) for future use without copy and paste this code again.

Thank you

 

[GraH]

Strange question as Power Query is a script that runs on refresh.
What exactly is your need? What do you try to accomplish?

 

[anna99]

Strange question as Power Query is a script that runs on refresh.
What exactly is your need? What do you try to accomplish?

Hi Grah, what i meant is i want to save all steps that i created in excel query, so that in future i can use all these steps again for the same data type format. hope it's clear

 

[sandy666]

maybe

 

[GraH]

ADVERTISEMENT

But PQ saves those steps...
Still not clear. One can export the connection file and reuse it as well.

 

[GraH]

@sandy666 , that would be equivalent of copy/paste of code, no?
@anna99 , Maybe you need to think PQ more. There are techniques where you can reuse the Query on another file. Using a variable, reading files from folder and select latest, move file to a dedicated folder, rename a source file,... Are a few options.
Hence my earlier question to elaborate on the use case.

 

[sandy666]

ADVERTISEMENT

@sandy666 , that would be equivalent of copy/paste of code, no?

something like that but much easier without opening Power Query Editor then Advanced Editor, Close all and so on

 

[Norie]

If the only thing that changes in the query is the source then you could set up the code to read the path to the source from a named range.

 

[anna99]

@sandy666 , that would be equivalent of copy/paste of code, no?
@anna99 , Maybe you need to think PQ more. There are techniques where you can reuse the Query on another file. Using a variable, reading files from folder and select latest, move file to a dedicated folder, rename a source file,... Are a few options.
Hence my earlier question to elaborate on the use case.

Hi Grah, i always use that for the table i have on an active worksheet. i don't save or get it from a folder. so i think there is no way to save this code somewhere in PQ, i will copy and paste when i need then. thank you anyway

 

[GraH]

So you have multiple files with some data in a table, structures the same way.
Meaning you can have another file with your PQ which you can connect dynamically to any of the files. Like @Norie mentions you can have a cell filled with the path to the source file and use this cell content as a variable in the source step of your PQ.
That way you do not need to copy the query each and every time.