Forum Discussion
Solved
Power Query Source from Relative Paths
Hi All,
I am making an analysis tool that uses two Excel file sources via Power Query. The tool work well, but as soon as the file and source files are moved to another location it stops working as the Queries sources from aboslute paths.
Can I modify my Sourcing so that as long as the Analysis tool file and the source files are in the same folder the Query will find and use the correct files? So that the Query sources from a path relative to the Analysis Tool file.
Thanks!
Magnus
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>
27 Replies
Hi All
My source doesn't end with null and true. Rather it looks like the below.
Source = Csv.Document(File.Contents("E:\PQ\testing\home.txt"),[Delimiter=" ", Columns=36, Encoding=1252, QuoteStyle=QuoteStyle.None]),When I do all the steps mentioned above, I get an error "4 arguments were passed to a function which expects between 1 and 3."
Would appreciate the help.
Thanks!
Which exactly steps do you do?
Hi Sergei.
Thx this works for me aswell...
An amendment would be to get files from a relative folder location instead of a specific file.
This is how I did this
let
WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
FullPathToFile1 = WBPath & "subfolder\subfolder2\",
Source = Folder.Files(FullPathToFile1),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"
Maybe this vba code helps:
Function FilePath()
Application.Volatile
FilePath = Application.ActiveWorkbook.Path & "\base.xlsx"
End FunctionActually the question is how to pass that into Power Query, doesn't matter how filepath was generated.
- Hello Sergei,
Thank you it's very useful for local files but how to make it work with a one drive folder?
the CELL fomula gives an html path.
Thank you very muchI guess we speak about the files synced with OneDrive. CELL("filename") returns in this case URL. To query the files you may use
= SharePoint.Files("https://tenant-my.sharepoint.com/personal/account", [ApiVersion = 15])which returns the list of files with FilePath column. Parse what is returned by CELL() and filter on that above query.
