I want to add a column with the file name

[AdamFraser]

***Please do not link to other answers - they are not adequate/relevant, I have checked them all. Hopefully this can help many people looking for the same answer.

The problem:

I am cleaning data from XLS files.

All XLS files are in the same folder.

I want to remove the first 8 rows, remove the last two rows, remove 3 columns, and promote the first row to headers.

This is straightforward and successful when performed on one file.

It is also successful when performed on all files in a folder, using a query, which creates one table with the same headers (the details of the query are not relevant).

However,

The source of each data entry (i.e., row) is lost.

I want to include a step in my cleaning process.

The new step will add a column to each XLS file.

The contents of the column will be the text contents of a cell in the XLS file.

This change would allow me to see the file source of each row.

Many thanks,

Adam

[Jimmy801]

Hello @AdamFraser 

when you are reading from a folder, there is the information of the path in the column "Folder path" and in the column "Name" is stored the file. Combine them both and you have the complete path of your file. Then apply your function that reads a file and sheet and does your transformation. Expand your new table and keep your new path-column. Thats it.

Here some code that could help you and the outcome

let
    Source = Folder.Files("YourFolder"),
    #"Added Custom" = Table.AddColumn(Source, "complete path", each [Folder Path]&[Name]),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Added Custom", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each YourFunctionToExtractTheContentOfExcelFile([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File", "complete path"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
    #"Expanded Table Column1"

Copy paste this code to the advanced editor in a new blank query to see how the solution works. (you have to put your folder and function in this code)

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Greg_Deckler]

@AdamFraser  I know that you can preserve the file name and even the path of the file for all rows. @edhans just answered that 2 days ago but you don't want a link.

I do understand you second part. Can you filter to a specific cell in an Excel file within Power Query. Yes. Where does this cell exist? If it is in the rows removed, you would put the logic in above those steps. Probably write a function. Could post a list to how to write a function to do it, but you don't want that either. 

Are you expecting the M code posted here to be perfect to your situation with no links to anywhere when you haven't even posted any sample data? I'm not sure how this question can be answered the way it is posted.

[AdamFraser]

No worries about @edhans solution, I read it but my problem is different.

No worries about M code, either. I'm just wondering what my approach should be. You said write a function, so I'll assume I can write a function that takes the text from a cell and uses it to populate a column. Thanks!

Adam