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>
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 much
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.
- SergeiBaklan, good day. Maybe you will be the right person who can help me. I have tried 2 options to open the list of files in the folder after copying of root folder on One Drive.
On PC in the file I have insert a formula
=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&C4
and named this cell "Path_Directions". It returns
E:\work\crossing forecast\Directions
on PC the next code in PQ works well:
folder = Table.SelectRows(Excel.CurrentWorkbook(), each ([Name] = "Path_Directions")){0}[Content]{0}[Column1],
Source = Folder.Files(folder),
But when I put the folder "Work" on One drive, formula of path have given:
https://fcbc1-my.sharepoint.com/personal/account/Documents/work/crossing forecast/Directions
And PQ stopped. So, I have changed the part of the Source code in two ways:
1st option:
folder = Table.SelectRows(Excel.CurrentWorkbook(), each ([Name] = "Path_Directions")){0}[Content]{0}[Column1],
Source = if Text.Start(folder,4) = "http"
then Web.Contents (folder)
else Folder.Contents (folder),
2nd Option:
Source = SharePoint.Files("https://fcbc1-my.sharepoint.com/personal/account", [ApiVersion = 15])
Both call the window: "We couldn't authenticate with the credentials provided. Please try again."
All levels have Anonymous access but it doesn't help. I cannot avoid this. Please help.If I understood correctly you query the files from the folder within the file which is in the same folder. Folder could be on local drive or on OneDrive for Business. If so, such construction shall work
let myOneDrive = "https://fcbc1-my.sharepoint.com/personal/account", folder = Table.SelectRows( Excel.CurrentWorkbook(), each ([Name] = "Path_Directions")){0}[Content]{0}[Column1], Source = if Text.StartsWith(folder, "https") then let oneDrive = SharePoint.Files(myOneDrive, [ApiVersion = 15]), myFolder = Table.SelectRows(oneDrive, each [Folder Path] = folder) in myFolder else Folder.Files(folder) in SourceFirst time you run it query asks you for data privacy. You may ignore it or set the same privacy (e.g. Organizational ) for all data sources - current workbook and files in folder.
Above works in my environment, I only used my actual "myOneDrive".
If the workbook in which you collect all data is in different from source files folder, that is another story.
I have a slightly different scenario. I have an MS Query connection in file1 to get data from file2.xls which is in a different folder to file1. I have used the code to find the relative location of file1 (i.e. =LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)) but to access file2 I need to back up before entering folder2 etc. Any idea how I can make that? The other approach I could think of is to get the relative reference to 'folder' meaning I can point to both file1 and file2 from the same reference
folder\folder1\file1.xls
folder\folder2\folder2.1\folder2.1.1\file2.xlsLooks like I'm working on something similar. Have a look at this snippet and see if it gives you any ideas. The gist here is I split the text, delete some bottom items, and combine it again.
let FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1], #"Split Text" = Text.Split(FilePath, "\"), #"Removed Bottom Items" = List.RemoveLastN(#"Split Text",3), #"Combine Text" = Text.Combine(#"Removed Bottom Items","\"), FullPath = #"Combine Text" & "\Projects.xlsm", Source = Excel.Workbook(File.Contents(FullPath), null, true), Projects_Sheet = Source{[Item="Projects",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Projects_Sheet, [PromoteAllScalars=true]) in #"Promoted Headers"
