Change source link in power query

[albertan]

I appreciate if somebody please help me with this. I'm trying to find the way to change the source file from one month to another by using the parameter table. I tried to follow the articles but unsuccessful.
What I did is recorded the connection to a file and made some modification. I have the path seen in Advanced Query. I'd like to have this path to be changed automatically through reference to another parameter table which I created. I named that with one cell and called it as Filepath. I tried to do it the way it was explained here
https://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/#comment-292750

But it is not working. I think the article refers to the fact that the query needs to be in the file itself and the file can be used elswhere. But my situation is different.

I just want to change a source file by putting a new path in a cell (table called Filepath) and put the reference to this Filepath in a Advanced Query of the table that is grabbing the data from the source file. Thank you!

 

[Rudi]

Hi,

I'm not sure if I understand all the points you mentioned, but see if the attached template and screenshots provide you with some answers regarding parameters, and folder/file references in your query.







View attachment Test.zip

 

[albertan]

Hello Rudi,

Thanks for this information. I wanted to clarify with you on why we have 7 queries in this workbook. Depending on your answer I would appreciate if you will be able to answer some other questions.

Thanks

Albert

Hi,

I'm not sure if I understand all the points you mentioned, but see if the attached template and screenshots provide you with some answers regarding parameters, and folder/file references in your query.

View attachment 7587

View attachment 7588

View attachment 7589

View attachment 7590

 

[Rudi]

Hi Albert,

The new format of "Combine" (see images below) produces the several queries you refer to. Before this feature was introduced, one needed to create a manual function query to provide the steps to use when you required PQ to iterate through the multiple files in a folder. This new format removes the need (and technicalities) of creating this function query and the parameters that go with it. The moment you click the combine icon, it all gets created for you. Instead of me writing out a whole tutorial on what each individual query does in this group, I'll point you to this very well documented tutorial of this new combine feature, written by Matt Arlington.

The reason for my posting the sample file was not to dig into the combine feature (though you are welcome to ask questions), but to provide you a sample of using "live" parameters based on values typed into the spreadsheet. I was hoping that this would lead you to some answers for your post.

Anyways, feel free to query further and I trust the images below and the link will provide the necessary feedback you need.

Cheers

 

[albertan]

Rudi

Please refer to these articles. I was trying to understand them better and apply them in my situation.

https://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/#comment-292750

https://powerpivotpro.com/2015/10/share-and-refresh-power-query-with-local-links/


I have a source file that I am getting every month with the updated data. It is being saved every month in a subsequent folder, attributed to a month (i.e. 2017/November, 2017/December etc.)

When I recorded the power query, in Advanced Editor it recorded the path which contains that month (i.e. February). Of course I can update the source manually, but I was trying to make it dynamic so that it can refer to a named table with the updated path that contains the word "March" (for example), however not successful. I'm using the VBA code to pick the file though a file dialog picker (this is not a problem). The problem is to edit the Advanced Editor and make it dynamic so that it can refer to the cell with the updated path (after the updated file was picked). Also, I'm trying to understand if I need to put the path for the folder where the file is resided or the full path with the name of the file itself. In Matt's example he refers to a file that will have the same name every month ( i.e. test.xlsx). I will keep on trying his example, but so far not successful. Thanks

 

[Rudi]

Hi,

The links that you refer to are "old". Written in 2014 and 2015 respectively. There has been a LOT of improvements to PQ since then so although the data in the links can still be implemented, the subsequent changes to PQ make the process of linking to source data much easier and convenient. In these later version of PQ, one can use a parameter table in a worksheet to determine the path and filename of a single file to import and transform, but you can also have the parameter table contain a path and folder and allow PQ to import all the files from the folder, or even a single file from the folder if you include filters in the query definition.

So, whether you have a fixed path in the parameter table with a second cell for the folder to scan (my first choice), or if you have a macro to browse for the path as a string and then deposit it into a cell as a parameter (over complication IMHO), the query will be able to handle what you pass to it. It all comes down to the query definition and if it works on a single file or a folder with multiple files.

What version of PQ are you running?
Also, can you attach a desensitized copy of the workbook with your query so I can see what you have build and the definition of your parameter table?

 

[albertan]

I wouldn't say those links are old There's still discussion going on there if you look at the trail of comments. I cannot attach the spreadsheet, I'll have to modify the source and put something simple.

My question was about changing the source directory with the reference to a path from a parameter table which can be changed with a VBA.

I appreciate if other experienced members could please comment?

 

[Rudi]

If you need the flexibility to change the string in a parameter table and have the query update to collect data from that new path or file, then this sample should provide you the answer. I have tweaked it a little from the first file sample I posted in my reply to your OP, but the concept remains the same: A parameter table contains the strings to make up the path. Whether it is typed into the parameter table, or inserted by a macro. The queries collect the string and combine it into a valid path and the data is extracted and transformed from there. If this is still not what you need, then I don't understand your query and a sample might be useful.

Cheers

View attachment Combine Data Template.xlsx

 

[albertan]

I'm getting this error message:

"The query table couldn't be refreshed:
The connection could not be refreshed. We could not determine the workbook that contains this connection. Please try refreshing this connection again in a moment."

Once I pick another month in a data validation dropdown, I can see that the data gets updated inside of the Edit Query (Get Data - Query Editor) however does not get loaded into an existing worksheet. What could be the reason? Perhaps I need to recreate a new query and load one month and then modify the formula with existing parameters and then later delete the original GetData query?

 

[Rudi]

Have you tried creating a few sample files with a basic list structure in them.
Then adding them into a sub folder
Then making sure the path, folder and filename in the parameter table point to the sample files.
Oh, BTW: Make sure also that your file name contains the extention part of the file to. (eg: .xlsx or .xlsm, etc)

In the image below, I get the following error if I do not have and folder or source files if the location that the parameter table points to.

Bottom line is that you need files in a folder. Make sure your parameter table contains the right data to point to the files. The source step in the queries is using the parameters, so that won't be the problem.

 

[albertan]

Thanks Rudy, I will make few tests and try to work it out.

On your computer, if you move source files from Feb folder to March folder, are you able to refresh the table?

what is the recommended sequence, when starting a file from new? (i.e. set up a parameter table first and then get a source file and make edits to it and then replace the path with parameter path)?

 

[Rudi]

If you move files from the Feb folder to the Mar folder, you would need to update the parameter table to display Mar. If this is the case, you can refresh the table and it will update with March.

The amount of flexibility depends on the design of the parameter table and the source step in your queries. Based on the design of your parameter table (see the image below) you can set up any number of different query scenarios. A table can be updated to display data from a different file (eg: Feb.xlsx to Mar.xlsx) by changing Feb to Mar in cell B6 in the parameter table. Likewise, if you change the Source Folder from (eg: Source Files to Source File Last Year), Feb.xlsx could display Feb 2016 instead of Feb 2017. Finally. if you choose to change the Path, you can switch and display files from another root folder or drive. The reason this is possible is because the source step in the query uses the values from the parameter table to build the path dynamically to the source file to display/refresh.



The sequence in creating a new file/template is not important. The two processes are separate designs that you eventually link together. For example; I can build the query first, importing the Feb.xlsx file to create the base query. After this I can create the parameter table and import this into PQ and set up the individual parameter values. Once I have the base query and the parameter values, I can go to the base query and modify the Source step and replace the hard-coded path with the parameters. If I choose to first set up the parameter values and later import the Feb.xlsx file, I will still need to edit the Source step to contain the parameters. Since i design these types of templates frequently for work purposes, I have a saved parameter template I use to start with. The parameters already exist and I then start to import the base files. So if i had to give my opinion, I'd start with the parameters and them build the source queries.

I hope your tests work out for you and that it brings you clarity and solutions.

Cheers