Forum Discussion
Power Query Source from Relative Paths
Hi Magnus,
You may find the current file path putting in any cell of your workbook the formula
=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)name this cell somehow, let say "FilePath" and generate absolute path in query like
<prev step> FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1], FullPathToFile1 = FilePath & "Name1.xlsx" <next step>
Hi Magnus,
You may find the current file path putting in any cell of your workbook the formula
=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)
name this cell somehow, let say "FilePath" and generate absolute path in query like
<prev step>
FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
FullPathToFile1 = FilePath & "Name1.xlsx"
<next step>
Hey Sergei,
Could you please help me? My query sheet source is a folder, not a file, so every time a file is copied to the folder, the sheet is supposed to update. However, this only works on my device. I want to make the path relative so it works on any device. I followed similar steps for a single file, with some modifications to make it work for a folder, but it's not working.
In my sample file, my source looks like this in the Advanced Editor:
Power Query
Source = Excel.Workbook(Parameter1, null, true),
In the actual file, my query source looks like this:
Power Query
Source = Folder.Files("C:\adfads\adsfa\adfd"),
How do I make the relative path work on any device?
Thank you!
Hey ArA2055,
Not sure if you ever got this answered elsewhere. But in the event someone else ends up here, it's done like this:
In your current excel sheet, copy this formula into any cell to get the current path where this sheet is located:
=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)
Name that cell whatever you want (which will change it to a named range) in this e.g. FilePath.
In the query you made with the source Folder.Files, open the advanced editor and drop this code just after the opening of your let formula.
FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
Then in the line which says Source, you'll use the previous step (FilePath) as the path. So in your e.g. you should have this
let
FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
Source = Folder.Files(FilePath)
in
SourceThis way, it grabs all the files in the same folder as the current excel document.
If you wanted to grab one specific file then it would be:
let
FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(FilePath & "Workbook name.xlsx"), null, true)
in
SourceSo essentially you replace "C:\adfads\adsfa\adfd" with FilePath
Hope this helps. Also, if you want to put the files in a subfolder instead, then in the cell you can concatenate the formula with the subfolder e.g.:
=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)&"\mysubfolder"
If we speak about local drive it's enough to use instead of
Source = Folder.Files("C:\adfads\adsfa\adfd")parameter which defines the folder
Source = Folder.Files(pFolder)Entire parameter could be defined / calculated in within Excel grid and queried. Perhaps it could be done relative, but it's not clear relative to what? We may find in which folder this file is located (e.g.=INFO("DIRECTORY"), but it shall be some logic where is the folder related to that file.
