Forum Discussion

Srini1053's avatar
Srini1053
New Member
3 years ago
Solved

Convert Text Data Type to Date?

Hi All,   I am newbie here. I am trying to convert text data type to date, but i am getting an error.  Could you please help me on this?  
Need Help
  • Ahmedx's avatar
    Ahmedx
    3 years ago

    plse try this

    try
Date.FromText("01/"&[Date], [Format="dd/M/yy", Culture="en-EN"]) otherwise 
try Date.FromText("01/"&[Date], [Format="dd/MM/yy", Culture="en-EN"]) otherwise Date.From(DateTime.LocalNow())

    or

    try
Date.FromText("01/"&[Date], [Format="dd/M/yy", Culture="en-EN"]) otherwise 
try Date.FromText("01/"&[Date], [Format="dd/MM/yy", Culture="en-EN"]) otherwise null

     

hashtag_pete's avatar
hashtag_pete
Helper V
3 years ago

Hello Srini1053 

then you need to to a bit of conversion first. Add a 1 for the first of the month and change / to .

The code would be the following, you just have to adapt the source

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] <> "YTD")),
    #"Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each let splitDate = Splitter.SplitTextByDelimiter("/", QuoteStyle.None)([Date]) in Text.Combine({"1.", Text.Combine(splitDate, ".")}), type text),
    #"Parsed Date" = Table.AddColumn(#"Custom", "Parsed Date", each Date.From(DateTimeZone.From([Custom])), type date)
in
    #"Parsed Date"

hope it helps - if so, kudos and mark as solution are appreciated. 

 

Hashtag_pete