Forum Discussion
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?
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
No it's not working.
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