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.
Power Query Relative Path Steps
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:Usersjpo64DocumentsDownload 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:
- 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.
- 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!
Comments (44)
Historical comments preserved from the WordPress archive. Commenting is no longer active.
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.
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.
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 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.
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?
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’.”
I got it to work now. Thanks for your help.
How’d you fix it?
Thanks!! This worked very well!
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.
That worked perfectly, thanks!
Love to hear it, thanks!
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!
Please, Marry Me!!!!
Thanks!!
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/Laptop@Home/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.
That’s very kind. I really appreciate that!
Hi Brant,
How did you manage to connect to the Access database? I’m trying the same but can’t get it working.
This is my code:
Source = Access.Database(File.Contents(Filepath & “database.accdb”), [CreateNavigationProperties=true]),
This is the error message that I receive:
DataFormat.Error: The supplied file path must be a valid absolute path.
Details:
Filepath:database.accdb
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
Great point!
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.
That was easy, thanks.
still got it!
Thank you! This works well!
Thanks for the tip. I pulled your first line to a blank query, as the entire query. I can then simply use the name of this query in any of the other queries I create. To me, this is a more simple way and a best practice to use the relative path in multiple queries.
Thanks for this Help 🙂
thank you so much, this works so well!
Thank for you this “How to”
After some editing i got this to work, by translate to Norwegian
Instead of using “filename” i need to use “filename”
And for separator in formulas you uses comma ,
I where needed to replace to semicolon ;
The formula became like this
=LEFT(CEL(“filename”;$A$1);FIND(“[“;CEL(“filename”;$A$1);1)-1)
I retained the English words in this post just so other people with another language may also understand how to
In Power Query there where no need for translate only edit to Quotation mark
Filepath = Excel.CurrentWorkbook(){[Name=”Filepath”]}[Content]{0}[Column1],
I love your video!
I tried to do this myself, but get an error “Expression.SyntaxError: Invalid identifier.”
It refers to the name used in Excel
Any idea how to fix this?
Thanks for the hint! 🙂
This really works. I got it going have never used box or power query much before! Thanks so much.
I am trying to adopt your process for a csv file. i have added some columns with some processing so I don’t want to lose that …
but: i get an error message basically from the beginning. excel points out that something is wrong with my FilePath.
If I type into a cell “=FilePath” it returns the value. So it should be ok …
but … here is my complete macro ….
FilePath = Excel.CurrentWorkbook(){[Name=”FilePath”]}[Content]{0}[Column1],
Quelle = Csv.Document(File.Contents(FilePath & “sale-report.csv”),[Delimiter=”;”, Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#”Höher gestufte Header” = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true]),
#”Geänderter Typ” = Table.TransformColumnTypes(#”Höher gestufte Header”,{{“Vorgangsnummer”, Int64.Type}, {“Verkaufsdatum”, Int64.Type}, {“Veranstaltungsdatum”, Int64.Type}, {“Veranstaltungsname”, type text}, {“Ermäßigung”, type text}, {“Preisliste”, type text}, {“Typ”, type text}, {“Art”, type text}, {“Anzahl”, Int64.Type}, {“Kartenpreis”, type number}, {“Zahlart”, type text}, {“Vertriebskanal”, type text}, {“Verkauft von”, type text}, {“Kundennummer”, Int64.Type}, {“Kundennachname”, type text}, {“Kundenvorname”, type text}, {“Kundenadresse”, type text}, {“Kunden-PLZ”, Int64.Type}, {“Kunden-Ort”, type text}, {“Kunden-Email”, type text}, {“Kundenland”, type text}, {“Kundenmerkmale”, type text}, {“gehört zu Vorgang”, Int64.Type}})
in
#”Geänderter Typ”
For one (local saved) document this works fine. Any ideas on how to manage a query with a local folder.
I could create a relative folder path for the query:
let
FilePath = Excel.CurrentWorkbook(){[Name=”FilePath”]}[Content]{0}[Column1],
OrdnerInhalt = Folder.Files(FilePath),
But I am having troubles setting the relative FilePath for the example file (first file in the folder)
Also cloud files/folders dont work for me with relative paths.