Dynamic Query path (Power Query)

[b0unce]

Hello,

Is there a way to set dynamic querry paths in Excel? Example:
I have a Master file which uses connections to 3 files which all have the same structure. The reason there are 3 files is because there are 3 people who must fill in their data. Then I created a querry in this Master file with links to those 3 files. My files are saved, let's say in C:/Documents. All in the same folder (both input files and Master file). If I copy all the files from C:/Douments to D:/Working files, then the Master file stops working as Querries are looking for data in C:/Documents folder.
So the question is whether there's a possibility to use dynamic file paths in querry connections so that when I copy files to different locations, the path in the querry is updated as well? All 4 files (Master file and input files) will always be in the same folder.

Thank you!

 

[Matt Allington]

Matt, i want to be precise.
When You move the file to the new location, and You will not open the file in the new location, then there is an old path to the folder in the file
But i think, this is not this case

Yes, that is what I would expect. If you open the file then it will update, but if you don’t then it won’t.

 

[vrsharma]

Hello Team,

I am very new to this power query, I tried the above solution, but it is not working for me, can anyone suggest what i am doing wrong.
I also don't where this changes I need to made, I got little bit idea form "bOunce" code, so I done changes by Clicking on Table -> Data -> From Table/Range -> In Applied Steps (under "Source")
Below is the code I tried in Power Query:
let
Path = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(Path & "CHS Library growth.log")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Count", type text}, {"Time", type text}, {"Date", type text}})
in
#"Changed Type"

It just shows code and nothing else when i press enter.
I have put formula in column "A1" and renamed it form "A1" to "FolderPath" and by Table start from column "C" to "F"

Thanks to advance