cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
doctornick0 Frequent Visitor
Frequent Visitor

Add column to convert source name to date

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Add column to convert source name to date

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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




3 REPLIES 3
Super User
Super User

Re: Add column to convert source name to date

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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




doctornick0 Frequent Visitor
Frequent Visitor

Re: Add column to convert source name to date

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

Highlighted
doctornick0 Frequent Visitor
Frequent Visitor

Re: Add column to convert source name to date

Thank you so much, this worked perfectly!