get current file name

[klauselt]

Hello,

I need a solution for getting the file name of the file, I am currently working with.

The filename contains the project number and the file ist automatically created and named.

I want to extract the part of the project number to use it for data filtering in a query.

So it is necessary to get the "current.workbook" name. 

Has anybody an idea? Thank you for your help.

[rohit_singh]

Hi @klauselt,

You can also try something like this :

1) Instead of connecting directly to the file, use the "Folder" data source and connect to the folder where your file is located. Dong this will give you a list of files in the directory within the column "Name". This will also contain the file that you need to connect to

 2) Then use the Text between delimiters option (or whatever is suitable to your case) to extract the project name from the file name as shown 

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!

[klauselt]

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 🙂

[BA_Pete]

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

[BA_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

[klauselt]

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 ...)

[BA_Pete · Accepted solution]

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