PowerQuery from one workbook to another

[Mr_Phil]

Hi, I have a template workbook that imports reports that are excessiily junked up. I can run a power query on each file by opening the file and then doing data>from range>transform. But, the number of files varies daily. On a 3-4 file day it isn't too much toopen each and recreate the query. But, honestly I'd like to save the query and then run it from the template to the reports. Once they are cleaned up I can then combine them using a different query to load to the template for saving as the daily file.

I know how to run macros from one workbook to others. But I am so new to power query that I am unsure of whether it will do this or not.

I am always grateful when someone reads and gives me insight, advice, or just another perspective. Thank you for looking.

 

[johnny51981]

Are the source files, of which the number can vary, the same in structure and just different in Data?

If so, you can use a Folder as a Source in your Power Query. I just set one up as a test, and it guides me through from selecting the Folder through to it being one query ready for me to tweak, if needed, or begin using how needed. It also automatically set up the necessary Functions. This should work each time you refresh the Template Workbook, regardless of the number of files.

 

[Mr_Phil]

Are the source files, of which the number can vary, the same in structure and just different in Data?

If so, you can use a Folder as a Source in your Power Query. I just set one up as a test, and it guides me through from selecting the Folder through to it being one query ready for me to tweak, if needed, or begin using how needed. It also automatically set up the necessary Functions. This should work each time you refresh the Template Workbook, regardless of the number of files.

Thanks for that. The source files are structured the same in that the first, second, and last columns are always the same. They are always selected and then unpivot the "other" columns. Then they get reordered to the desired look. The issue for me is that each file can hold up to a dozen names in between first, second, and last column. That limit of 12 names means no file will ever go past column O (oscar). I have discovered that I can wrap the m code inside VBA and run the macro that way. So I am now googling how to m code so it will always select first, second, and last (last will always fall somewhere from D to O) regardless of one name or a full dozen.

I took your advice about a folder and did a test run or three to get a feel for it. I like it, thanks for that in particular. Once I can get the language tweaked I think I can wrap it all up into a control button to do the clean up and then bring in the cleaned data to the template via that folder. You helped me get another step down the road. I appreciate it more than I can type.

 

[Mr_Phil]

Hi, I have a template workbook that imports reports that are excessiily junked up. I can run a power query on each file by opening the file and then doing data>from range>transform. But, the number of files varies daily. On a 3-4 file day it isn't too much toopen each and recreate the query. But, honestly I'd like to save the query and then run it from the template to the reports. Once they are cleaned up I can then combine them using a different query to load to the template for saving as the daily file.

I know how to run macros from one workbook to others. But I am so new to power query that I am unsure of whether it will do this or not.

I am always grateful when someone reads and gives me insight, advice, or just another perspective. Thank you for looking.

To fully answer the question and hopefully provide a bit of pay it forward: There is no built in way to run a query from a template or master workbook onto another workbook. But, you can get there if you are creative. A lot of thanks to johnny51981 for his advice about working with a defined folder as it allowed final resolution of my issue.

If you use the macro recorder to capture your query steps you can then code the macro to run from your master/template.

First create or designate a folder to hold all the files needing to be cleaned up.

Then open your workbook you need the query to work on and build it until the output is what you need. Once you like it, close without saving. Then reopen it and engage the macro recorder. Name it what you wish and store it in that workbook. Then build your query. Once you close and load it, you can disengage the recorder.

Go to the VBA editor and copy the macro. It will have all the data needed to run the query. You can again close the target without saving.

Open your master workbook and paste the VBA into a module. From there you can “wrap” it with code needed to open the target workbooks within that file path, run the query, then save and close the target. With a loop function you can then move through all the files as needed.

Once those are done, you build a second query in your master/template to import all of the now-cleaned files in the folder. Once that is done you are done building and can enjoy your success.

Now, daily, I bring in all the files I need to the TODAY folder. From the Master I fire off the macro to clean up the individual files via PQ which leaves all of them with a table that is clean. Then I just refresh the import query on the Master. Once that is done I do a “save as” on the Master by adding a date to the file name. I’m on an iPad right now so I can’t post the final code, but if I can remember I will post a reply with the code from my laptop a bit later.