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

     

Srini1053's avatar
Srini1053
New Member
3 years ago

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

 

mlsx4's avatar
mlsx4
Memorable Member
3 years ago

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's avatar
    Srini1053
    New Member
    3 years ago

    No it's not working.

    • mlsx4's avatar
      mlsx4
      Memorable Member
      3 years ago

      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