Forum Discussion
Change from absolute to relative path in Power query
Please check this thread https://techcommunity.microsoft.com/t5/excel/power-query-source-from-relative-paths/m-p/206150
I was trying that but I get an error
let
WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
FullPathToFile1 = Table1 "filename.xlsx",
Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true),
Expression.SyntaxError: Token Comma expected.
I didn't catch what the Table 1 is in your script. Let me illustrate on my sample.
Here
we have the cell named filepathCELL which returns full path of the current file. With that entire script could be
let myAnotherFile = "dataFile.xlsx", thisFilePath = Excel.CurrentWorkbook(){[Name="filepathCELL"]}[Content]{0}[Column1], onlyPath= Text.Split(thisFilePath,"["){0}, fullpathAnotherFile = onlyPath & myAnotherFile, Source = Excel.Workbook(File.Contents(fullpathAnotherFile)) in SourcePlease note that works only on local folders, with folders synced with Onedrive/Sharepoint CELL() returns web-like file path (http://...)
- Is there any way to make this work with one drive synced folders (like Desktop)? This works fine if I move the files to an external drive, but my File Path returns a URL when running from my desktop.
That's the same, take part of URL till last slash, add filename and you'll have path fot another file in same folder. Query it as
= Excel.Workbook(Web.Contents("https://MyPath/NewFile.xlsx"), null, true)
I added the Table1 trying to figure what to do
WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
FullPathToFile1 = WBPath & "Filename.xlsx",
Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true),If I try this one, I get
Expression.Error: We couldn't find an Excel table named 'WBPath'.
Details:
WBPathyes, here shall be Table1 in your case
Excel.CurrentWorkbook(){[Name="Table1"]}[Content]{0}[Column1]
