Convert Text Data Type to Date?

[Srini1053]

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?

[Ahmedx · Accepted solution]

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

[Srini1053]

Thanks alot @Ahmedx . I got the required solution.

[hashtag_pete]

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

[Ahmedx]

Is this what you are looking for?

[hashtag_pete]

Hello @Srini1053 

you could filter on <> "YTD", so you get rid of all text. Then try again and it should work. 

BR

hashtag_pete 

[Srini1053]

I tried filtered out YTD, but still getting an error.

[mlsx4]

Hi @Srini1053 

You must do first the step of filtering YTD and then change to date format. In the steps on the right, check the order 

[Srini1053]

No it's not working.

[mlsx4]

Hi @Srini1053 

Ok, the problem is that m/yy is not recognized by PowerBI. Maybe you can do this transformation: 

let
    Origen = Excel.Workbook(File.Contents("C:\Users\Desktop\ex.xlsx"), null, true),
    Hoja1_Sheet = Origen{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Encabezados promovidos" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"Date", type text}}),
    #"Filas filtradas" = Table.SelectRows(#"Tipo cambiado", each ([Date] <> "YTD")),
     #"Agregar columna personalizada" = Table.AddColumn(#"Filas filtradas", "Personalizado", each Text.Combine({"0", Text.Start([Date], 3), "0", Text.Middle([Date], 2)}), type text),
    #"Columnas con nombre cambiado" = Table.RenameColumns(#"Agregar columna personalizada",{{"Personalizado", "New Date"}}),
    #"Columnas quitadas" = Table.RemoveColumns(#"Columnas con nombre cambiado",{"Date"}),
    #"Columnas con nombre cambiado1" = Table.RenameColumns(#"Columnas quitadas",{{"New Date", "Date"}})
in
    #"Columnas con nombre cambiado1"

Just in case you don't understand this code... Once you are in the step of <>TD, add a customized column with this code:

Text.Combine({"0", Text.Start([Date], 3), "0", Text.Middle([Date], 2)})

And then simply remove the other column and keep the new one. You will be able to convert to date now

[vicky_]

Have a read of https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#datetime-types

your problem is that you have "YTD" which can't be converted into a ddate. A valid date format might look like 01/01/2023.