August 6

How to create a relative file path in Power Query

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!

Loved this? Spread the word


About the Author

Jordan Goldmeier

Related posts

All Excel LOOKUPs Explained

Read More

How to: Power Query File From Folder

Read More

2020 – I can’t wait!

Read More

Oz’s Excel Tip: Keep a Workbook for Random Data in Excel

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

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

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

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

  • 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:
    Z:FILES2019PROJECT.xlsx

    This is the code I have in the editor:

    let
    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.
    Detalles:
    Key=
    NAME=FILEPATH
    Table=[Table]

    I don’t know how to solve this error.

    Thank you very much in advanced.

    Regards

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

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

  • 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: https://d.docs.live.net/xxxxxxx/[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,

    Benjamin

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

  • Hi Jordan. Really cool stuff. I thought about your question regarding the cell reference argument in the CELL() function after the “filename” parameter. It would be useful if you wanted to have the filename of the other open workbook. I tried this and CELL() only returns the name of the other workbook (no path).

    Example: Current workbook: [Power_Query_Unpivot_Daniel.xlsx]
    [Power_query_combine_budget_actual_Daniel.xlsx]Actual!$A$1

    Also isn’t it cool that if also return the name of the sheet where the ref is located. Can possibly see that useful in VBA.

    Daniel

  • Hi, Does this trick also work if I want to read in a set of csv files from a folder, or do I need to modify it still further? In my case, not only will the filepath change, but the number of csvs within the folder may also be variable.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Subscribe to our newsletter now!

    >