Power Query Source from Relative Paths

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
Source

This 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
Source

So 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"

ArA2055 

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.

JeffersonValim 

Actually the question is how to pass that into Power Query, doesn't matter how filepath was generated.

Clemleb 

I 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.

I ahd some of the same problem, for me, the error was the "Let" in the start of the code was with a Capital letter. When I switched to all lowercase letters it worked fine.

So it should maybe be:

let
WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
FullPathToFile1 = WBPath & "file.xlsx",
Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true),
<next step>

I copy/pasted your code into PQ

let
    WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
    FullPathToFile1 = WBPath & "file.xlsx",
    Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true)
in
    Source

There are no errors at all