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:

=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)

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!

Sign up to excel your life with tips, career advice, and more!

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

    • Jordan Goldmeier says:

      I knew it was a lookup of some sort. It’s interesting to me that M/PQ uses a numeric for the row and [Column{n}] for the column title. The latter makes sense in my brain but row reference is kinda funky.

  • 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
    =MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255)

    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.

    • Jordan Goldmeier says:

      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:

    let
    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}})
    in
    #”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’.
    Details:
    C:UsersJason.GettyBox190101.09 HP PV190101.09 HP PV (EXT)RFIsHPPV RFI MASTER SET LOG LINKS.csv

    What am I doing wrong?

    • Jordan Goldmeier says:

      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?

      • Jordan Goldmeier says:

        Nevermind! I just tested that wordpress removes those. Ok, well, we can dispel that weird thought.

        But it is telling you it doesn’t like the filepath.

    • Jordan Goldmeier says:

      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.

  • Jordan Goldmeier says:

    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.

  • >