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

     

     

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

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Share the link from where i can download your PBI file.

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

      Hi,

       

      Share the link from where i can download your PBI file.

       

      is there another way, i cannot provide you with the file as it has some highly confidential data?

       

      thanks

       

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

      Hi,

       

      Share the link from where i can download your PBI file.

       

      the dates that seem to be in question are ones that include this format, so far everything else seems to work fine to convert

       

      Oct 8, 2018 08:42:10 AEDT

       

      wondering how i can convert this?

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi Anonymous,

         

        It seems that you want to convert the column First Discovered Column to a consistant format.

         

        Is the data type of column First Discovered Date/time?

         

        If it is, that is a little strange. Pleasea dummy pbix file which can reproduce the scenario, you could only share one column you want to change, so that I can have a tests on your file? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

         

        In addition, please share desired  output.

         

        Best Regards,

        Cherry