Forum Discussion
Change from absolute to relative path in Power query
I often have to change the actual path of my workbook, or share it, so I want to change the query location in a way that as long as both files are in the same directory, they work Source = Excel....
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]
Ive try modifying it but then I get this error later
Expression.Error: We cannot apply operator & to types Record and Text.
Details:
Operator=&
Left=[Record]
Right=Filename.xlsx
