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

[alexbjorlig]

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

[alexbjorlig]

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!

[hackcrr]

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.