Forum Discussion

efilipe's avatar
efilipe
Icon for Helper IV rankHelper IV
9 years ago
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?

 

Thank you!

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    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"

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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's avatar
      MarcelBeug
      Icon for Community Champion rankCommunity Champion
      9 years ago

      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"