Forum Discussion
Solved
Text to date
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? ...
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"
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"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"
