Power Query – source with relative path
This articcle describes the relative (or, ehm, pseudorelative...) referencing in Power Query (Get and Transform). I mean instead of hard reference like this:
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, automatically changed based on actual position of the currently opened file.
All you need is this formula:
Something like this appears in cell:
and it is changed automatically according to folder.
When placing to cloud, something like this appears:
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.