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
Import from folder: use part of filename as value in addl. column
-
- Posts: 1019
- Joined: Sun May 04, 2014 4:01 pm
- Location: Sydney, Australia
Re: Import from folder: use part of filename as value in addl. column
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.
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
Power BI Training
Re: Import from folder: use part of filename as value in addl. column
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

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
Who is online
Users browsing this forum: No registered users and 1 guest