Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
alexbjorlig
Helper IV
Helper IV

Is it possible to load a DateTime column into Power BI from OData v4?

I'm a developer of an OData API, and also a Power BI maintainer.

When I return a column like this in OData:

 

<Property Name="Date" Type="Edm.DateTimeOffset"/>

 

 

Power BI always parses this as a "Date/Time/Zone".

Is it possible to specify a type, or format of the date, so Power BI will parse the column as "Date/Time"?

(If I could achieve this, it would be really great for query folding and incremental refresh).


2 REPLIES 2
alexbjorlig
Helper IV
Helper IV

Hi there, thanks for trying to help.

If I format the date as ISO 8601 "2024-05-30T12:00:00" format, I get the following error:


DataSource.Error: OData: Cannot convert the literal '2024-05-01T00:00:00' to the expected type 'Edm.DateTimeOffset'.

 

And yes, I can convert the column after loading the data into Power BI, but then I can't fold the query!




CleanShot 2024-05-16 at 16.59.05@2x.png

hackcrr
Super User
Super User

Hi, @alexbjorlig 

You can specify the date format in the OData API response so that Power BI resolves the column to "Date/Time" instead of "Date/Time/Region". However, this may depend on the capabilities of the OData server and the way it handles data types and formats. Here are some suggestions on how to accomplish this:
Set the date format in ISO 8601 format without time zone offset:
<Property Name="Date" Type="Edm.DateTimeOffset"/> Instead of returning the date in a format, you can format the date as a string in ISO 8601 format (without a timezone offset). For example, "2024-05-30T12:00:00" represents a date without a time zone offset. This format is widely recognized and should be resolved by Power BI as "date/time" regardless of time zone.

If the OData API does not directly control the format of the date in the response, you can use Power BI's Query Editor to transform the data. After loading the data into Power BI, you can use the query editor to parse the date string into the Date/Time data type using the DateTime.FromText() function and specify the appropriate format.

 

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors