Forum Discussion

lastnn30's avatar
lastnn30
Icon for Post Patron rankPost Patron
4 years ago
Solved

Changing column type to date or time giving me error message.

Hi

I have this excel table which has a column called Starting Date which is formatted as text not date. So I used PowerQuery to transfer it to Date. When I Get Data --> From Excel Table, PQ editor and the data type of the column is (Calendar and Clock) which gave me an impression it is transform to date and time. The when I click on Calendar and Clock icon and then click on Calendar, I get error message!

 

But when I click on Add Column -->Date and TIme -->Date -->Date only

 

I would get a new column with date only. Now when I go back to the Starting Date and change the type to Calendar, it would change it to Date only without error message! Why is that?

 

Thank you very much

 

The file can be found here:

https://docs.google.com/spreadsheets/d/1ahYP9QtL1GP3h6G7rWTTzZqQSiIyPYIg/edit?usp=sharing&ouid=100398295280512397391&rtpof=true&sd=true

 

++++

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi lastnn30 ,

     

    Because Power Quey does not allow text in "mm/dd/yyyy hh:mm:ss" format to be converted directly to Date type. We need to convert it to Date/Time type first, and then convert Date/Time type to Date type.

     

    That is, if you select "Replace current", it will go from Text -> Date and report an error.
    If you select "Add new step", it will go from Date/Time -> Date, with success.

     


    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi lastnn30 ,

     

    Because Power Quey does not allow text in "mm/dd/yyyy hh:mm:ss" format to be converted directly to Date type. We need to convert it to Date/Time type first, and then convert Date/Time type to Date type.

     

    That is, if you select "Replace current", it will go from Text -> Date and report an error.
    If you select "Add new step", it will go from Date/Time -> Date, with success.

     


    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.