# Dynamic Query path (Power Query)



## b0unce (Oct 27, 2017)

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 (Oct 27, 2017)

No, power query can't automaticly follow your file movements. I normally place all files in 1 folder, and then use get data\file\folder. This will process all files in the one folder. You can split the query in 2 so that the first query points to the folder and the second combines the files. That way if your location changes you can just change the first query.


----------



## billszysz (Oct 28, 2017)

Matt, there is a trick we can use. )We can do it with a little help from excel.
Put somwehere in your Master file this formula and give it a Name (for example your formula is in A1 - Name for A1 "FolderPath")

```
=LEFT(CELL("filename",A1),SEARCH("[",CELL("filename",A1))-1)
```

Then you can use this construction in your PQ query.

```
let
    Path = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1],
    Source = Folder.Files(Path),
...
...
    LastStep = [I]something[/I]
in
    LastStep
```
and Voila.... now your path to the folder can follow your file )

Regards


----------



## Matt Allington (Oct 29, 2017)

Very good idea.  But does this cell value refresh if you don't open the file ?  eg if you save the file, close the file, move the file, then does it show the new file location or the old one.  I assume the old one


----------



## b0unce (Oct 30, 2017)

billszysz said:


> Matt, there is a trick we can use. )We can do it with a little help from excel.
> Put somwehere in your Master file this formula and give it a Name (for example your formula is in A1 - Name for A1 "FolderPath")
> 
> ```
> ...




Hi billszysz,

thanks for your reply. Could you please help me a little bit with tweaking the PQ Query? It's completely dark forest for me  Before making any changes (and everything is working fine), I have the following sequence:

```
let
    Source = Excel.Workbook(File.Contents("C:\Users\paul\Desktop\QueryTest\DataInput.xlsx"), null, true),
    Prices_tbl_Table = Source{[Item="Prices_tbl",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Prices_tbl_Table,{{"EAN code", Int64.Type}, {"Product", type text}, {"Landed cost, €/piece excl. VAT", type number}, {"Price list price, excl. VAT", type number}})
in
    #"Changed Type"
```

When I follow your suggestion, I simply replace the part of Source = .... to the one you indicated and therefore my code becomes this:

```
let
    Path = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1],
    Source = Folder.Files(Path),
    Prices_tbl_Table = Source{[Item="Prices_tbl",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Prices_tbl_Table,{{"EAN code", Int64.Type}, {"Product", type text}, {"Landed cost, €/piece excl. VAT", type number}, {"Price list price, excl. VAT", type number}})
in
    #"Changed Type"
```

Doing so I immediately get this error:


```
Expression.Error: The key didn't match any rows in the table. Details:
Key = Record
Table = Table
```

Could you please help me with this error? Have no clue how syntax of PQ should look like.
Thanks!


----------



## billszysz (Oct 30, 2017)

Matt Allington said:


> eg if you save the file, close the file, move the file, then does it show the new file location or the old one.  I assume the old one


The new one ))


----------



## billszysz (Oct 30, 2017)

b0unce, "FolderPath" is only the path to the folder and not to the file. You have to filter out your desired file

let
    Path = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1],
    Source = Folder.Files(Path),
    #"Filtered Rows" = Excel.Workbook(Table.SelectRows(Source, each ([Name] = "DataInput.xlsx")){0}[Content]),
    Prices_tbl_Table = #"Filtered Rows"{[Item="Prices_tbl",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Prices_tbl_Table,{{"EAN code", Int64.Type}, {"Product", type text}, {"Landed cost, €/piece excl. VAT", type number}, {"Price list price, excl. VAT", type number}})
in
    #"Changed Type"


----------



## billszysz (Oct 30, 2017)

or this code below

let
    Path = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(Path & "DataInput.xlsx")),
    Prices_tbl_Table = Source{[Item="Prices_tbl",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Prices_tbl_Table,{{"EAN code", Int64.Type}, {"Product", type text}, {"Landed cost, €/piece excl. VAT", type number}, {"Price list price, excl. VAT", type number}})
in
    #"Changed Type"


----------



## billszysz (Oct 30, 2017)

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


----------



## b0unce (Oct 30, 2017)

Works like a charm (the second option)! Thank you very much!!!




billszysz said:


> 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


----------



## b0unce (Oct 27, 2017)

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 (Oct 30, 2017)

billszysz said:


> 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 (Sep 9, 2019)

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


----------

