Import from folder: use part of filename as value in addl. column

[pscheffc]

Hello,
I have a use case where I have multiple csv (or in another case .xlsx) files in a directory which I want to combine using Power Query. The contents of the files is referring to a day or week, the specific date is part of the filename only, but not part of the contents of each file. However, to do "over time" reporting, I need that date to become part of each record (row) in the files.

Is it possible to create an additional column as part of the import process and populate that column with an extract (e.g. characters 12-21) of the source file name?

E.g., file names are "staticname_YYYY_MM_DD.<extension>"
Additional column name should be named "report date" and have the date as content: DATE(YYYY,mm,dd)

Thanks for any hints, Peter

[MattAllington]

Yes. What version of Excel do you have? Excel 2016 should do this almost automatically. Simply combine and edit. The auto process should add the source file name to the import. From there you can you extract the date information from the file name to use it in your table.

Post back if that doesn't make sense.

[pscheffc]

cool! worked like a charm, I do have 2016

Next stage of complexity, assume that the source file has 7 "footer lines" at the bottom, and one of these 7 lines does contain a text string like: "Generated By: John Doe 6/26/2017 11:11 PM" and I again want to extract the date and put it into a new column. Would that be possible, too?
Peter