Date Format Problem

[Marke525]

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?

[v-easonf-msft · Accepted solution]

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.

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.

[mahoneypat]

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

[amitchandak]

@Marke525 , refer to the old solution

http://geekswithblogs.net/darrengosbell/archive/2019/02/01/power-bi-ndash-fixing-dates-from-sharepoi...

[Marke525]

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