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

pscheffc
Posts: 2
Joined: Mon Nov 20, 2017 7:07 pm

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

Postby pscheffc » Mon Nov 20, 2017 7:13 pm

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
Posts: 1007
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

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

Postby MattAllington » Tue Nov 21, 2017 6:06 pm

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.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

pscheffc
Posts: 2
Joined: Mon Nov 20, 2017 7:07 pm

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

Postby pscheffc » Wed Nov 22, 2017 10:53 pm

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


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 5 guests