Forum Discussion

Magnus Vegem Dahle's avatar
Magnus Vegem Dahle
Brass Contributor
Jun 20, 2018
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 ...
excel
Formulas and Functions
Get & Transform
Power Query
  • SergeiBaklan's avatar
    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>

     

Anonymous's avatar
Anonymous
Aug 16, 2018

Hi All,

 

I have the Power Query add-in on Excel 2013 and Windows 7. I tried the following:

 

I named cell A1 of the workbook "WBPath" and entered this formula in it:
G:\Ibrahim Omar\Flooring\Sales Analysis\
And with this, the cell A1 now shows the path of the workbook correctly.

 

Then I started my query with the following code:

 

Let
WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
FullPathToFile1 = WBPath & "file.xlsx",
Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true),
<next step>

 

I immediately and without even closing the editor got this  error below:
"Token Eof expected."

 

When I clicked show error, it highlighted the first instance of "WBPath" in my code.

 

So, what is this error? And how can I fix it?


Thanks a lot!

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 16, 2018

I copy/pasted your code into PQ

let
    WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
    FullPathToFile1 = WBPath & "file.xlsx",
    Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true)
in
    Source

There are no errors at all

 

  • Rup231470's avatar
    Rup231470
    Copper Contributor
    Jan 09, 2023

    Hi

    I am new to power query , and I want to convert my absolute path to relative path,

    SergeiBaklan I used the same code and did not get any errors but,

    It was giving me the null value as output

     

    I gave cell A1 as the name WBPath and this is the code I am using

     

    let
    WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
    FullPathToFile1 = WBPath & "file.xlsx",
    Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true),

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 09, 2023

      Rup231470 

      Hard to say without the file. If to repeat

      Code:

      let
WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
FullPathToFile1 = WBPath & "testa.xlsx",
Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true)

in
    Source

      Result:

       

  • Anonymous's avatar
    Anonymous
    Aug 16, 2018

    That's really weird! Could it be that mine is Excel 2013 and Power Query hasn't been updated since March 2018? Or should this work regardless?

     

    And thanks a lot for responding, Sergei!

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 16, 2018

      I don't think something was significantly changed during past months, but in any case better to keep PQ updated.

Resources