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
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
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