Dynamic Query path (Power Query)

b0unce

Board Regular
Joined
Apr 22, 2009
Messages
75
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!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.
 
Upvote 0
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")
Code:
=LEFT(CELL("filename",A1),SEARCH("[",CELL("filename",A1))-1)

Then you can use this construction in your PQ query.
Code:
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
 
Upvote 0
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 :(
 
Upvote 0
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")
Code:
=LEFT(CELL("filename",A1),SEARCH("[",CELL("filename",A1))-1)

Then you can use this construction in your PQ query.
Code:
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:
Code:
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:
Code:
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:

Code:
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!
 
Upvote 0
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"
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top