How to create a relative file path in Power Query - Excel TV

How to create a relative file path in Power Query

What do you think of the video thumbnail? I was trying to go for Excel Vaporwave.

In this blog post, we'll talk about how to create a relative file path. I'm sharing this because it's a common problem: the client asks me to build something, which happens on my computer, but then I need to send it to them. If the file path is absolute, as you'll see in this example, the client won't be able to correctly run the query. 

The instructions to create a relative path are as follows. 

Download Files: Click here to download the 2 files used in this tutorial. 

1. To use Power Query hack, first connect to another spreadsheet using Power Query.

You can do this by going into the Data Tab and selecting Get and Transform, select From File and select the desire Excel file you'd like to load to Power Query.

In this case, I've selected the local file I'm interested in called Sample Table.xlsx.

2. When finished, select Close and Load To.

If you choose to edit the table first, you'll select Close and Load To in the Power Query editor. Alternatively, you can skip this step and simply load it directly into the spreadsheet by hitting the Load button in the Power Query dialog box. 

3. Next, create a new worksheet tab called Setup.

Right click on the bottom of the worksheet and add a new tab. I recommend calling it Setup but you can also call it Backend, Setings, etc. It's important however to be explicit. Why? Because it's good spreadsheet practice, that's why. 

4. In a desired cell add in the correct formula. 

The correct formula appears as follows:


Let's break down this formula. The first piece is CELL("filename",$A$1). If you type this into Excel, it'll pull back the entire filename, path, and sheet. In our sample data, it looks like this: 

C:\Users\jpo64\Documents\Download Files Powe Query\[Sample Power Query File.xlsx]Setup

Note, we really only want everything until that first "[," also called a "left square bracket." 

So to parse out what we need, we use the FIND() function to find the first (and only instance) of the left square bracket in the filename. In our example, it'll return a 26, indicating it was found as the 26th character in the entire string. That means, if we want everything before that square bracket, we're actually interested in the first 25 characters (that's why we subtract by one in the formula). 

Finally, we'll use LEFT (which pulls a certain amount of specific text starting from the left) to pull out the file path from the entire stringe. In this case, we'll need to get the full filepath again, so we repeat the CELL("filename", $A$1) in LEFT's first parameter. 

5. Name this cell Filepath

To give Power Query access to this cell, we'll need to give it a specific name. The easiest way to do that is to name it Filepath in the named range box, and then hit enter. 

6. Doubleclick on your query and open the Power Query Editor

There are a few ways to open the Power Query editor for the specific query, but I like to click it in the popup pain until the editor appears. 

7. Open the Advanced Editor

Once inside Power Query, open the Advanced Editor from on the Home tab. 

8. Above the source, place the following text:

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

Then edit the Source variable. 

Source = Excel.Workbook(File.Contents(Filepath & "Sample Table.xlsx"), null, true),

If you did it right, it'll look something like this:

Some final thoughts

If you're having trouble, there are two main areas where I've found people have been tripped up:

  1. They forgot add a comma at the end of their M code instruction. Remember, each line ends with a comma up until right before the "in" keyword.
  2. M is case sensitive, so if you write "FilePath," say, and not "Filepath," your Power Query will error out. That's not the case with regular, old-fashioned Excel. 

How would you use this? Don't forget to share your thoughts in the comments. 

Let us know in the comments!

  • Bob Phillips says:

    That first line pulls in a named range from Excel, as a list. The {0} gets the first row of the list, and the [Column1] gets the first column (PQ names the columns Column1, Column2, etc.). In your case, you don’t need them as your range is a single cell, but you would need it if it were a multi-row, multi-column range.

  • Bob Phillips says:

    The A1 in the Excel formula is used to tie the formula to the sheet it is on, it is often used to get the sheet name, like so

    If you omit the cell reference, and activate another sheet, it can show the other sheet.

    For example, put that formula in M1 on Sheet1. Then goto Sheet2 and type =Sheet1!M1 in any cell and see what you get.

    It can be any cell on the worksheet, A1 is just used for simplicity/uniformity.

    For the workbook name, you probably don’t need it, but best to get into the habit.

    • I figured as much, that the reference would allow you to pull from another location. I’m just always thinking about how I can cut down on things, even if I don’t in practice.

      • Bob Phillips says:

        I am exactly the opposite, I always prefer not to cut down as I feel it inevitably leads to loss of clarity, or worse, breaks somewhere down the line. For that reason I abhor property defaults, you will never see me doing
        some_var = Range(“A1”)
        and omit the .Value property.

  • Jason Getty says:

    I’m trying to get this to work on an existing workbook that queries a CSV file. Here is what my power query looks like:

    Filepath = Excel.CurrentWorkbook(){[Name=”Filepath”]}[Content]{0}[Column1],
    Source = Csv.Document(File.Contents(Filepath & “HPPV RFI MASTER SET LOG LINKS.csv”),[Delimiter=”,”, Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #”Promoted Headers” = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“File Name”, type text}, {“Set Name”, type text}, {“Page Number”, type text}, {“File Path”, type text}, {“CSI RFC #”, Int64.Type}, {“THJV PCO #”, Int64.Type}})
    #”Changed Type”

    I get the following error:

    DataSource.Error: Could not find file ‘C:UsersJason.GettyBox190101.09 HP PV190101.09 HP PV (EXT)RFIsHPPV RFI MASTER SET LOG LINKS.csv’.
    C:UsersJason.GettyBox190101.09 HP PV190101.09 HP PV (EXT)RFIsHPPV RFI MASTER SET LOG LINKS.csv

    What am I doing wrong?

    • It’s telling you it can’t find the file. I’m struggling with the file path given in the error. When I look at ‘‘C:UsersJason.GettyBox190101.09 HP PV190101.09 HP PV (EXT)RFIsHPPV RFI MASTER SET LOG LINKS.csv’.” I see no slashes…. is there a reason for that? What is the value in your Filepath cell?

    • I would check to see if the file is in location pointed to by the Filepath. Then I would double check the name hardcoded into M is the same as the actual file.

  • test: ‘‘C:UsersJason.GettyBox190101.09 HP PV190101.09 HP PV (EXT)RFIsHPPV RFI MASTER SET LOG LINKS.csv’.”

  • Jason Getty says:

    I got it to work now. Thanks for your help.

  • Fernanda says:

    Thanks!! This worked very well!

  • DANIEL says:

    Hi Jordan,
    Thank for your post. I have a problem to use this relative file with a referenced cell.

    I have to say that the named range (named “FILEPATH”) is a cell (column 3 – Row 20) located in a worksheet named “LINKS” in which I have directly pasted the path as text:

    This is the code I have in the editor:

    FILEPATH = Excel.CurrentWorkbook(){[NAME=”FILEPATH”]}[Content]{0}[column1],
    Origen = Excel.Workbook(File.Contents(FILEPATH), null, true),

    This is the error Power Query reports:

    Expression.Error: The key didn’t match any rows in the table.

    I don’t know how to solve this error.

    Thank you very much in advanced.


    • Aran says:

      I think I had a similar issue. I think I got it working when I changed Filepath from a named range to an Excel table using Insert Table. The table contained only one row (and a column header row), which contained the file path formula.

  • BlueNote says:

    That worked perfectly, thanks!

  • Sean M says:

    Hi, I’m doing a PowerQuery that brings in Excel files from a folder, not a single file. Does this change how we would do the formula. I’m trying to merge multiple Excel files together using PowerQuery, but as you said, I am doing this for another organization so the folder path I have would change.

    Does that make sense? Is there a way for me to use a relative file path to fix this? Thanks

    • Conceivably it wouldn’t change. You’d have to read in the file name as I discuss. Look at the M code where the folder path is harded coded in and replace it with the M variable you created. But I’ll be honest and tell you I’ve never tried it before. Good luck!

  • Victor says:

    Please, Marry Me!!!!


  • Benjamin says:

    Hi Jordan,
    thank you for sharing this code. I tried to get this running with a file saved on my onedrive. After working with Aran’ suggestion to use the the insert table (which worked, thx), i got the error “DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.” which means that the path need to be an valid absolute path.

    Mine looks like this:[email protected]/Projekte/XXXX/Bestellungen_01.01.2019-30.04.2020.xlsx

    Do you have any idea why this is not working?

    Thank you so much,


  • Brant says:

    Just wanted to say thank you. I found this to be helpful today. I tweaked it a bit because my data source was an Access database. I thought you did a much better job at breaking the process down than other posts about this topic.

  • >