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:
- 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, automatically 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
and it is changed 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.
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?
=LEFT(CELL(“filename”),FIND(“[“,CELL(“filename”))-1)&”source.xlsx”) <—– too many right braces ")"
this is not written well at all. i’m amazed at the total amount of useless information there is on this topic that people have to sift through to learn any of the power features. a real testament to the cluelessness of the tech industry as a whole. i don’t know how people who work in your industry are going to survive once the tech bubble crashes. the majority of you are incapable of understanding your end user and as an industry you are severely lacking in basic job skills.
Please correct the formula:
=LEFT(CELL(“filename”);FIND(“[“;CELL(“filename”);1)-1)
Hey i’m trying to put the path of the cell but doesn’t work
can you specify the part of getting the path of the cell?
Thanks in advance
Fizzgig:
1) “filename” – just write “filename” there
2) write it to any cell
this dont work when You documents folder is synced to onedrive
=CELL(“filename”;A1)
https://d.docs.live.net/cd1fc9b8ea51aec2/Documents2/%5BTestPath.xlsx%5DLeht1
this is what produced when use CELL filename
This formula works perfectly. Previously, I solved the relative path thru VBA with the need of refresh.