How to get a criteria in a column based on the filename of the data source

[Philipp_ISARTAX]

We are preparing a statistic where all paricipants provide a xlsx-table in the same format and structure. The table itself does not include a value for the providing participant. 

The identification of the participant is only possible according to a participant-Code which is part of the xlsx-filename.

What I have done so far:

I have copied all files in one folder and imported the data with Power Query from the folder as source. 

After that I combined all tables to get one table with all data. 

Now I want to add a column which gives me the filename from which xlsx-File the data comes or which shows the code which is included in the filename.

Otherwise I'm actually not able to analyse the data based on the participants.

I hope I have described my issue propperly.

Kind regards,

Philipp 

[Jonvoge · Accepted solution]

Hi Philipp

If you are using a Folder connection, you can use the Combine Files option to create a query which will include the file name for you to use:

Even if your files should not be combined, you can simply filter down your query to only include one file at a time, before using the Combine button to only expand that single file.

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data

[Philipp_ISARTAX]

Hello Jon,

Thank you very much for your support. This helped already a lot!

Is there also a possibility to include the Folder Path as column in the table? 

Background is, that the connected folder has some subfolders which I would like to identify.

Many thanks in advance

Kind regards,

Philipp