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]

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]

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]

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]

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

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]

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]

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]

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]

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]

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

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