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

anna99

New Member
Joined
Jan 8, 2021
Messages
13
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
696
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Strange question as Power Query is a script that runs on refresh.
What exactly is your need? What do you try to accomplish?
 

anna99

New Member
Joined
Jan 8, 2021
Messages
13
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,416
maybe
saveM.png
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
696
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
696
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
@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

Well-known Member
Joined
Oct 24, 2015
Messages
7,416

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 8, 2021
Messages
13
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
@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

Well-known Member
Joined
Mar 22, 2020
Messages
696
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,784
Messages
5,574,243
Members
412,579
Latest member
ffbaker632
Top