Change from absolute to relative path in Power query

Iron Contributor

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.Workbook(File.Contents("C:\Users\me\Documents\file.xlsx"), null, true),

 

I want that as long as file.xlsx is in the same directory as my workbook, it will find it

 

Source = Excel.Workbook(File.Contents("\file.xlsx"), null, true),

 

Ive try this but does not work

19 Replies

@Sergei Baklan 

 

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.

@Ocasio27 

I didn't catch what the Table 1 is in your script. Let me illustrate on my sample.

Here

image.png

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
    Source

Please note that works only on local folders, with folders synced with Onedrive/Sharepoint CELL() returns web-like file path (http://...)

 

@Sergei Baklan 

 

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:
WBPath

@Ocasio27 

yes, here shall be Table1 in your case

 Excel.CurrentWorkbook(){[Name="Table1"]}[Content]{0}[Column1]

@Sergei Baklan 

Expression.Error: The field 'Column1' of the record wasn't found.

@Sergei Baklan 

 

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

@Ocasio27 

I'd recommend you to do things step by step for better understanding how Power Query works.

1) Create in Excel sheet the Table as here

image.png

You may use any table and column names you prefer, here they are tblFilePath and FilePath accordingly.

2) Query it - stay on Table and on ribbon Data->From Table/Range

3) If you didn't change default options, most probably you will see two generated steps, Source and Changed Type. Let keep them, result is

image.png

3) We need extract text with filepath now, that's first element in FilePath column. Since numbering in Power Query start from zero, let add in formula bar to above [FilePath]{0} and Enter. Result will be

image.png

and Power Query shows two steps

image.png

We still have two steps adding Navigation to second one. It returns the text with filepath.

4) Now we need to extract from it only current folder name. That will be the text before [ , in our case c:\test\. Thus we may split our text on two parts - before [ and after it, and take first part only. First let click on Fx near formula bar

image.png

In formula bar the name of the previous step is shown, let wrap it by formula and Enter

image.png

5) As result we have a list with two parts of filpath text. We need first element of the list. Remembering that numbering starts from zero, add {0} to formula and Enter

image.png

6) To combine with target filename (let say myTarget.xlsx) to generate its full path in current directory, again Fx

image.png

and in formula bar add the text

image.png

 

Result will be in Custom2

image.png

and we may use it for accessing Custom2 file in same folder

If open Advanced Editor, generated code will be

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="tblFilePath"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FilePath", type text}})[FilePath]{0},
    Custom1 = Text.Split(#"Changed Type", "["){0},
    Custom2 = Custom1 & "myTarget.xlsx"
in
    Custom2

 

 

We may create function from it, or at least to make bit more compact and readable - we don't need type changing here and better to have step names which say us what we do

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="tblFilePath"]}[Content][FilePath]{0},
    currentFolderPath = Text.Split(Source, "["){0},
    targetFilepath = currentFolderPath & "myTarget.xlsx"
in
    targetFilepath

 

 

@Sergei Baklan 

 

I get error

 

Expression.Error: The column 'FilePath' of the table wasn't found.
Details:
FilePath

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][FilePath]{0},
    currentFolderPath = Text.Split(Source, "["){0},
    targetFilepath = currentFolderPath & "My_File.xlsx"
in
    targetFilepath

@Ocasio27 

That only means this column in the table has another name.

@Sergei Baklan 

 

But I am not trying to get a specific column, I want the whole table

 

@Ocasio27 Entire specific table doesn't return the value, even if it is only one cell with data in such table. To get such value you shall reference on the table first, after that on column where the values is (doesn't matter it's only one column), after that on first element in this column (doesn't matter it's only one element in it).

@Sergei Baklan 

 

Ive try to substitute the value with the first column name, which is "RECORD" and what it returns in the value of the first row in first column and the name of the file filename.xlsx

@Ocasio27 

When it's most probably

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][RECORD]{0}

 

@Sergei Baklan 

 

I am still not getting the results expected. Could you please post 2 excel files linked? one with 2 different tables the other with the queries.

@Sergei Baklan 

 

Ok I managed to fix it, now the only remaining problem is that it requires a cell =CELL("filename") which is something I cannot do with PowerBI

@Ocasio27 

=CELL("filename")  is within Excel sheet, that's not Power Query function. You query result from the range/table in the sheet.

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.

@RobDerby 

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)