Dynamic Query path (Power Query)

b0unce

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

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,189
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

Active Member
Joined
Feb 26, 2014
Messages
344
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
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,189
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

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

billszysz

Active Member
Joined
Feb 26, 2014
Messages
344
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

Active Member
Joined
Feb 26, 2014
Messages
344
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

Active Member
Joined
Feb 26, 2014
Messages
344
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

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

Forum statistics

Threads
1,084,920
Messages
5,380,583
Members
401,688
Latest member
Empa

Some videos you may like

This Week's Hot Topics

Top