Text to date

[efilipe]

Hi guys!

I have a field on CVS that has date like DDMMYYY. It's giving me errors on dataloading, because my field is set to date type. I'd like to change it to DD/MM/YYYY.

Any suggestion?

Thank you!

[Greg_Deckler · Accepted solution]

Here is one way. Given a source of something like:

date

30012017

29012017

let
Source = Csv.Document(File.Contents("C:\temp\powerbi\date1.csv"),[Delimiter=",", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"date", Int64.Type}}),
#"Inserted First Characters" = Table.AddColumn(#"Changed Type", "First Characters", each Text.Start(Text.From([date], "en-US"), 2), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted First Characters",{{"First Characters", "Day"}}),
#"Inserted Last Characters" = Table.AddColumn(#"Renamed Columns", "Last Characters", each Text.End(Text.From([date], "en-US"), 4), type text),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Last Characters",{{"Last Characters", "Year"}}),
#"Inserted Text Range" = Table.AddColumn(#"Renamed Columns1", "Text Range", each Text.Middle(Text.From([date], "en-US"), 2, 2), type text),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Text Range",{{"Text Range", "Month"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns2", "MyDate", each Date.FromText([Year] & "-" & [Month] & "-" & [Day])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"MyDate", type date}})
in
#"Changed Type1"

[MarcelBeug]

Alternatively you can use Splitter.SplitTextByPositions to split the input dates in day, month and year part.

Beware of automatic data typing when importing your CSV file.

As a basis, you can choose some format function on the Transform tab (e.g. Trim) and adjust to Splitter.SplitTextByPositions.

After splitting the input, you can combine the parts again with delimiter "/"  and

parse the result as a date with a culture code that supports dd/MM/yyyy format. 

This video was recorded while creating the code below.
I could have adjusted step name #"Trimmed Text" to something more meaningfull like #"Splitted Input Date".

let
    Source = Csv.Document(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\DDMMYYYY from CSV to Date.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Trimmed Text" = Table.TransformColumns(#"Promoted Headers",{{"AField", Splitter.SplitTextByPositions({0,2,4})}}),
    #"Extracted Values" = Table.TransformColumns(#"Trimmed Text", {"AField", each Text.Combine(List.Transform(_, Text.From), "/"), type text}),
    #"Parsed Date" = Table.TransformColumns(#"Extracted Values",{{"AField", each Date.From(DateTimeZone.From(_,"es-ES")), type date}})
in
    #"Parsed Date"

[dkay84_PowerBI]

Try Date.FromText:

https://msdn.microsoft.com/en-us/library/mt260703.aspx