Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Issues with Change type to Date/Time

hi,

 

i am trying to change the type of a date/time column to a consistant format but i get errors

when i change the type to a date or date/time in Power Query Editor i get this error

 

DataFormat.Error: We couldn't parse the input provided as a DateTime value.
Details:
    Oct 8, 2018 08:42:10 AEDT

 

since i have multiple formats of how the date is represented, what would be the best way to convert it to a consistant format?

 

in addition, i am not that fussed about the time, but more interested in the date

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    i worked out an easier solution

     

    since i do not need the AEDT, or AEST text i removed them in advanced query editor

     

    1. Duplicated Column (to not lose any data but not necessary)

    2. Right click on column and select Replaced Values
    3. Inserted AEDT in "Value to Find" and left "Replace with" as blank

    4. Right click on column and select Replaced Values

    5. Inserted AEST in "Value to Find" and left "Replace with" as blank

    6. Changed Type to Date/Time

     

    since all values now have a date & time i can use that in my graphs with no troubles now

     

     

10 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Right click on the column and go to Data > Split Column.  Select space as a delimiter and select Right most.  This will remove the everything after the last space.

     

    Does this resolve the problem?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      Ashish_Mathur wrote:

      Hi,

       

      Right click on the column and go to Data > Split Column.  Select space as a delimiter and select Right most.  This will remove the everything after the last space.

       

      Does this resolve the problem?

       

      sorry, i am not seeing that option