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

excel

Formulas and Functions

Get & Transform

Power Query

SergeiBaklan
Jun 20, 2018
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>

Magnus Vegem Dahle

Brass Contributor

Aug 17, 2018

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>

Deleted

Aug 17, 2018

GAAAAAAAAA!!!! You're right, Magnus. It worked!

I copied your code the first time and edited it without noticing the upper case "Let". Can you still edit your post to fix it after all that time?

Thanks guys!

Forum Discussion

Power Query Source from Relative Paths

Resources