Datatype issues in Sustainability CDM

[lnewportmsft]

For example, under Emissions, the following attributes are listed as dateTime:

calculationdate consumptionenddate consumptionstartdate transactiondate

However, it is necessary on import to apply a datatype transformation to "date" in PowerQuery for the import to succeed. Absent this transformation the import fails.

Should these be "date" in the Sustainability CDM?

[dingbx]

Hi @lnewportmsft , sorry we missed your message. I'll check the datatype problem with these attribute, at the same time, can you please elaborate how you import the schema in PowerQuery with repro steps?

[scheyal]

These are datetime with date-only format. It can be discovered by examining dataverse backend -

Refer to the following for date behavior doc: https://docs.microsoft.com/en-us/power-apps/maker/data-platform/behavior-format-date-time-field

[lnewportmsft]

Hi @dingbx - if you take this Excel sheet (https://aka.ms/AAhg2w0) and import it into Sustainability Manager for Precalculated Emissions via the following steps, you will see repro.

1. Import
2. Map to CDM (in Power Query).
3. These columns will be defined as DateTime
4. Import will fail with type error.

(Import works if after Map to CDM, DateTime is changed to Date) for each affected column.

[Venkata1920]

Hi @lnewportmsft , thanks for bringing this issue to attention. Yes, these need to be date in the CDM schema too. We will work with the Sustainability team on getting the relevant schemas updated in an upcoming release.

[scheyal]

The failure is b/c Calculation date is missing. It should work just fine otherwise.

[andresravinet]

The failure is b/c Calculation date is missing. It should work just fine otherwise.

I'm not sure you understand what issue we're trying to get resolved here.

[scheyal]

I don't see the issue here. Maybe you can explain in details again?

I tried a repro with the link.

That XLS was missing calc date (was null). When I import it (no manual or auto-mapping), PQ does a transformation as follows (and just succeeds):

XLS:

PQ view (not edited):

[dingbx]

Hi,

Just wanted to check the current status of this issue. Has the issue been resolved?

If I understand it correctly, the issue is the schema in Map to CDM dialog, which the source data has been mapped to, has some of the attributes defined with datetime, but their type should be date.

and these attributes are:

• calculationdate
• consumptionenddate
• consumptionstartdate
• transactiondate

If this is the root cause, then sustainability team can check their schema and update them if those types should be date. And if this is not the problem, it might be the validation should also accept datetime.

[dingbx]

Closing this issue since the explanation was provided through email, please reopen if the issue is not resolved.