Add column to convert source name to date

[doctornick0]

Hi, everybody,

When importing a folder of CSV files, I need to add a column to convert the file names (which are in my Source.Name column in Power Query) to dates, as the CSV files are snapshot files of the sales pipeline snapped as of that date.

I'm pulling the CSVs by month (each month represents 1 folder), so I know the given month and year, which I can apply to all records in the query, but the file name contains the day information.

The files are named in a format of either "Pace Report Data_050518.csv" or "Pace+Report+Data_012118.csv", in either case, all I care about is the 2 digits after the underscore.  The first file is the snapshot for January 05, 2018 and the 2nd is for January 01, 2018.  I'm not sure why Salesforce is pushing the files across with different file name formats, but the common item is always the underscore and the 2 digits following.

Given the above, is there a line of M code that I could use to create the new date column that will allow me to apply the above logic?

Thanks so much for your help,

-doctornick0

[ImkeF · Accepted solution]

Yes: You add a custom column with this code:

Text.Start(Text.AfterDelimiter([Column1], "_"),2)

Replace "Column1" with the name of your column.

You can paste this code into the advanced editor and see how it works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkhMTlUISi3ILypRcEksSYw3MDUwNbTQSy4uU4rVgchrQ+S1IfKGRoYw+VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Day", each Text.Start(Text.AfterDelimiter([Column1], "_"),2))
in
    #"Added Custom"

[doctornick0]

Thank you so much, this worked perfectly!

[doctornick0]

Dear Imke,

Thanks so much!  Is there a way to accept your solution?  The "Accept Solution" button gives me an "Authentication Failed" error.  Or, alternatively, is there a forum support mechanism I can reference?

Thanks for your help!

- doctornick0