This articcle describes the relative (or, ehm, pseudorelative...) referencing in Power Query (Get and Transform). I mean instead of hard reference like this:

C:\folder\subfolder\source.xlsx

you sometimes need reference the source file in the same folder as the output file. 

Probably the main reason to do it is storing files on cloud (OneDrive, Dropbox), where some folder is shared by multiple users on hard drives on different paths.

We will start with this tutorial, describing the referencing with absolute path.

No we will make this path relative, or, actually, automaticaly changed based on actual position of the currently opened file.

All you need is this formula:

  • =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&"source.xlsx")

Something like this appears in cell:

  • C:\Users\PC\Desktop\source.xlsx

changing automatically according to folder.

When placing to cloud, something like this appears:

  • C:\Users\mypc\Dropbox\source.xlsx

So the reference actually goes to the folder where both files take place, no matter which folder is it.

Well, I confirm this solution is definitely niether nice nor straightforward, So if you know anything better, please write it to discussion.

 

1 Comment

  1. Questions:
    1.) In the formula you show here, Is “filename” a named range, or is this just a reference to any file name, or is it both, or something else?
    2.) Where do you put the formula?
    3.) How does Power Query use or link with the Formula, and why would this even work without Power Query halting with an Error condition?

Leave a Reply

Your email address will not be published.

*

clear formPost comment