How to use a PQ on subsequent downloads

[SimonHughes]

Getting to grips with Power Query but I need some help as I am really not sure how I retrieve a query to use on subsequent worksheets. I download a raw CSV worksheet, transform it with Power Query to the finished worksheet and select Close & Load and after some formatting, save it as an Excel file in the appropriate folder.

I will then download another CSV for a different client that requires the same process as above (so I am not overwriting or adding to the first file) but how do I use the PQ protocol that I have used above?

This is basic stuff I know but I have not found the answer in the PQ tutorial videos that I have found. Thanks in advance.

 

[alansidman]

Open the original query. Go to Home-->Advanced Editor-->copy the Mcode you created. Go to the new CSV. Load your new table to the PQ editor. Go to Home-->Advanced Editor. Replace the Mcode with the code you copied from the original. Change the Table name if necessary in the new Mcode.

 

[SimonHughes]

Open the original query. Go to Home-->Advanced Editor-->copy the Mcode you created. Go to the new CSV. Load your new table to the PQ editor. Go to Home-->Advanced Editor. Replace the Mcode with the code you copied from the original. Change the Table name if necessary in the new Mcode.

Hi Alan, yep, that works a treat. So I just need to store that code somewhere safe (say in a text file) so that I can repeatedly apply in the worksheets that need the same formatting. Excellent, thanks.

 

[RoryA]

Personally I would set the query up with a parameter for the file name (from a named cell in a worksheet) then save that as a template, so you can reuse it.

 

[SimonHughes]

Open the original query. Go to Home-->Advanced Editor-->copy the Mcode you created. Go to the new CSV. Load your new table to the PQ editor. Go to Home-->Advanced Editor. Replace the Mcode with the code you copied from the original. Change the Table name if necessary in the new Mcode.

Ah well, not so simple. I have just r

Personally I would set the query up with a parameter for the file name (from a named cell in a worksheet) then save that as a template, so you can reuse it.

Hi Rory, sorry, but I do not know what you mean by your suggestion. Can you expand please? I do all the formatting in PQ and then save how exactly? And apologies if this is basic stuff.
epeated this and get the Error Message

 

[RoryA]

That error message means that your query refers to a column that is not in your source data.

What I mean is that you set up a workbook the way you want it, but make the file path part of your query be pulled from a cell on a worksheet (see for example here). Then save that workbook as a template.
Then all you need to do for a new data source is create a new workbook based on the template, update the file path cell and then refresh the query.

 

[SimonHughes]

That error message means that your query refers to a column that is not in your source data.

What I mean is that you set up a workbook the way you want it, but make the file path part of your query be pulled from a cell on a worksheet (see for example here). Then save that workbook as a template.
Then all you need to do for a new data source is create a new workbook based on the template, update the file path cell and then refresh the query.

Rory, I have worked out how to achieve what I want to do - by using Select from File >From Folder and then the Combine and Transform dropdown one can chose whether to append multiple files or select a single file for transformation. Very easy, I am sure that you will be familiar with this process and maybe my explanation of what I wanted to achieve was not as coherent as it could have been. But thanks for your help, appreciated.