How to use the current file location as the relative path so I don't need to reset all my sources?

[JollyRoger01]

I have an Excel file that references several source documents within a subfolder that sits in the same main folder (called 'Banking'). I zip this main folder and send it quite a lot when I work between different computers, however I am tired of having to relink the source data folder all the time.

I was wondering if I could use a relative path so it uses the current file location (Banking folder) with everyhing preceding it being relative. I know Python and many other languages can do this by default so I figured there must be some way to do the same in Power Query.

[watkinnc]

[edhans]

[JollyRoger01]

Sorry everyone but I am finding all this quite confusing. Can someone please tell me just what I need to type into the function field for the Source step I have in the picure below? I know nothing about Power Query and any answers without context I cannot interpret.

[edhans · Accepted solution]

Apologies for the confusion. It is tough when multiple people join in at once trying to help. It is like getting 3 different directions to the same place at once. did you try what I posted in my first reply? That told you exactly what to type in and how. And if you needed it to be from a worksheet cell, then use the cell() function as I described. 

then, click on that excel cell, on Data ribbon , use the From Sheet button (or from table-MS is in the middle of changing the names). 

that will load that cell in as a table into power query. Then,  right click on the value in that table with the path and select DRILL DOWN

Now rename that query varFilePath as I did in my example and use in the main query instead of the hard coded path. 

make sense?

https://community.powerbi.com/t5/Power-Query/How-to-use-the-current-file-location-as-the-relative-pa...

if still having issues post back with exactly the issue you are getting stuck at. 

[Anonymous]

I'm getting this error???? 

"Formula.Firewall: Query 'X' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

[watkinnc]

You sure can. In cell A1 of a worksheet, add the function:

= INFO("directory")

this will give you the current path. You can then just click get data from range, and you will have the path as a query that you can use as a variable  I would name the table or query something like varPath, and you might need to edit the path so that you have whatever part of the path you need to be dynamic.

Your path will end up as something like:

varPath&"FileName.xlsx" where varPath is the current path.

--Nate

[edhans]

Yes. Just replace the part of the path you want with a query. For example:

My variable, called varFilePath is just a text string that I typed in, but you can make it a formula that resolves to whatever you want. As long as it is a valid file path and text, it should work.

I've used this method with queries in Excel that were opened by different users where their network path was different than mine. PQ resolved based on a their name to their specific path.

[JollyRoger01]

Thank you for the reply. Is there a way to extract this automatically from the current Excel file path? So if a user opens the Excel file, it will gather the path before the Excel file name and save that as, say, varFilePath for example?

[edhans]

Yes @JollyRoger01 - you can use the CELL() function as shown:

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

You have to be careful though because sometimes when a user opens a file if it is on Office 365 or Onedrive you can get a path that looks like this:

You may need to adjust your code to discern the local path if it is a sync'd account or is in OneDrive/SharePoint.

You can use INFO() as @watkinnc shows but that is only if you know your files are always stored in the same place by everyone. INFO returns the environment for Excel's default save as path, not the location of a file. You need CELL to be file specific.

[Keith011]

Great solution! but how do i "adjust" the https:// issue for sharepoint files?

Error : The name 'varFilePath wasn't recognized. Make sure it's spelled correctly.