Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@Marke525 , refer to the old solution
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."
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |