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
Icon for Helper V rankHelper V
3 years ago

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

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

 

  • mlsx4's avatar
    mlsx4
    Icon for Memorable Member rankMemorable 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
        Icon for Memorable Member rankMemorable 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