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 klauselt ,
You may need to explain a bit more about what the "file you're working with" actually is but, in Excel, you can enter the following to get the full file path of the workbook. From there, you can text edit to get the portion you want:
= CELL("filename", A1)
Pete
Hi BA_Pete
The Excel file shows a summary of data from a specific named project from a larger data lake.
The query only fishes the relevant data for the named project from the data lake.
I want to make sure that this Excel file can be easily updated automatically every time it is opened.
The file is generated automatically and stored in the project drive.
Then the correct project number should be transferred to the query the first time the file is opened.
I have tried out you sollution:
CELL("filename", A1)If there is nothing else direct in Power Query, than this will be the solution.
Thank you 🙂
Now I can select the "Project No Part" out of the name.
But now I struggle with creating a "Function" to use the number inside a table to use it as a filter parameter (but this is another topic ...)
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