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

     

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago
v-piga-msft wrote:

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

 

doesnt look like its a date/time, well only the ones giving me problems dont seem to be date/time

 

here is the sample of the excel data i have pushed into Powerbi

https://1drv.ms/x/s!ACB0l6B7iYmP8CI

 

my desired output is just dd/mm/yyyy

 

thanks

 

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

Hi Anonymous,

 

By my test in Query Editor, we may could achieve your desired format in it. Please refer to the steps below.

 

1. Please duplicate your table.

2. Then filter the Rows by condition for the two tables.

3. Split column First Discovered by position for the two tables.

4. Remove other columns don't need.

5. Append query. Please keep the column names to be the same in the two tables.

 

The detail steps, please refer to the attachment.


Cherry
Best Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    v-piga-msft wrote:

    Hi Anonymous,

     

    By my test in Query Editor, we may could achieve your desired format in it. Please refer to the steps below.

     

    1. Please duplicate your table.

    2. Then filter the Rows by condition for the two tables.

    3. Split column First Discovered by position for the two tables.

    4. Remove other columns don't need.

    5. Append query. Please keep the column names to be the same in the two tables.

     

    The detail steps, please refer to the attachment.


    Cherry
    Best Regards,

     

     

    point 3, split column by position. i dont seem to have this option

    i only get split column by delimeter or number of characters

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    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