Forum Discussion

efilipe's avatar
efilipe
Helper 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?   ...
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"