Forum Discussion
get current file name
There's no function that I'm aware of in PQ that will get this info for you.
If you're able to add this cell onto a worksheet in your workbook and not have it overwritten, then this would be the simplest, as you could then just grab that cell into PQ and get your project number using a combination of Text.BetweeDelimiters and Text.Range.
Failing that, I think you'd have to go into VBA.
PS: For a huuuuge time-saver, I think you can actually write the Excel formula just as:
= CELL("filename")Pete
Hi BA_Pete
thanks for your idea. But that doesnt fit either.
The path of the last level folder is a project folder, has the name (and number) of the project and by this there is no chance to be defined in a global template.
Thist template is automatically taken and put into the project folder when the folder is created.
It is a kind of a basic setup of the project folder with all kind of basic templates inside, which all get the name of the project.
I want to realize, that you only must open the file and then you see only data of the correct project.
The data lake is huge and you will wait a long time, if you make a mistake with selecting the wrong project by yourselve.
Now I have tried out the solution and it works well. Then you must grab the Project no. out of a list, which needs a function (not so easy to find, but here I found the way: Building a Parameter Table for Power Query (excelguru.ca) )
I'm only supprised, that there is no direct PQ solution.
Thanks again for your idea 🙂
Hi klauselt ,
This last idea wasn't mine, it was from rohit_singh .
Obviously just a mistake, but I just wanted to make that clear as I purposely didn't suggest that method for exactly the reason you provided: you can't filter the folder list due to dynamic project numbers.
Pete