How to bulk change the source location for queries from different files in the same folder?

[FreemanZ]

How to change the source location for queries from different files in the same folder?

 

Supposing I have 10 Excel files of different dimentions in one folder. Someday, I rename the folder or I move the whole foler elsewhere. I can update the folder in the first Source step, for one query after another. Is there a good way to change the source easily? 

 

If all queries are from one excel file, it could be done with parameters. 

If all queries are from different excel files but with similar type and to be appended, it is also easy. 

 

Is there similar convinent way for queries from different files of different type/dimention in the same folder?

[FreemanZ]

hi Jorge, 

Many thanks for the quick reply.

Could you stipulate how it works? Or help google an relevant article or video?

i tried and failed. 

I get below:

at point 1, the parameter is used. at point 2, it is still with the raw folder path. 

If i replace the folder path with Source, I get an error. 

[FreemanZ · Accepted solution]

Thank you Jorge. I found this article works and solves the problem.

https://powerbi.tips/2016/08/using-variables-for-file-locations/

Adding the back slash at the end of the copied file path saves my day. Preciely the line below:

= Excel.Workbook(  File.Contents(   Folder  &   "2000 Medals.xlsx") ,   null , true )

Thank you Jorge to give me the hint and confidence working in this direction. 

[JorgePinho]

You're welcome 🙂 Well done!

[JorgePinho]

I reccomend you the following:

1. Create a parameter with the folder path

2. Go to New Source choose Folder

3. When it asks for the path change to parameter 

[PD_D]

Thanks a lot for sharing this trick, works well and saved a lot of time.. 

[JorgePinho]

The same way you can use parameters for one excel file you can also make for the folder path.