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