cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Marke525
Frequent Visitor

Date Format Problem

I am transfering a date from a calculated field in sharepoint.  In sharepoint I have set the output as "Date and Time" with a "Date Only" format.  When I transfer this to Powerbi, it appears as: 2021-01-25T23:33:42Z (for example).  Powerbi considers this text.

When I try to change the data type of the field to date, it says "We can't automatically convert the comlumn to Date type".  It also makes the same statement if I try to convert it to Date/Time format.

I also tried DateValue(SharepointDate).  This results in the error: "Cannot convert value '2021-01-25T23:33:42Z' of type Text to type Date."

WHat I need is this information to work

How do I get data from a calculated fiedl from sharepoint that is formatted as a date to behave as a date field in Powerbi?

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hi, @Marke525 

In value '2021-01-25T23:33:42Z' ,the T  is just the separator that the ISO 8601 combined date-time format requires. You can read it as an abbreviation for Time.The Z stands for the Zero timezone, as it is offset by 0 from the Coordinated Universal Time (UTC).

 

This type of data (text) can not be directly converted into a date format. As a workaround you can  create  a new column to extract the date.

60.png

61.png

62.png

Please check my sample file more details.

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

4 REPLIES 4
v-easonf-msft
Community Support
Community Support

Hi, @Marke525 

In value '2021-01-25T23:33:42Z' ,the T  is just the separator that the ISO 8601 combined date-time format requires. You can read it as an abbreviation for Time.The Z stands for the Zero timezone, as it is offset by 0 from the Coordinated Universal Time (UTC).

 

This type of data (text) can not be directly converted into a date format. As a workaround you can  create  a new column to extract the date.

60.png

61.png

62.png

Please check my sample file more details.

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

mahoneypat
Super User
Super User

I pasted your example text into a new query, and it recognized it and formatted it as DateTime.  However, if I replaced that step and went straight to Date, I got the same error.  Try first converting it from Text to DateTime.  If successful, change it to Date.  When you get the popup that asks if you want to replace or insert new step, choose insert new step.

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


amitchandak
Super User
Super User

Thank you for the suggestion.  Unfortunately, this does not work, it returns the error:  " We can't automatically convert the comun to Date/Time type."

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!