D365 FO/ Datalake / CDM / PowerBI

[Anonymous]

Hi all,

We've set up a datalake connecting to D365, and are now connecting to this through PowerBI - all the data comes through and at a glance everything looks great.

When connecting as filesystem I (as expected) have no headers, which is obviously problematic - but not an issue as we wanted to use the folder view.

When connecting CDM Folder View I have the headers, however there seems to be an issue with (some of) the dates.

The dates for some columns come through as expected, however some of the date columns (when I open in PowerQuery) come through as 'error' (every row has the same error), if i change the format to text (string) or a different date format the data still comes through as an error.

When opening in Tabular Editor i can see the date format is 'long date' - however the data is in this format : 

2022-05-16T00:00:00.0000000 - i've changed this to a more suitable format, or text but it still shows an error - though i must add i'm a novice on Tabular Editor, so it's possible i'm missing something there.

Is this an issue with the JSON, in that PowerBI is parsing for metadata for each column, and it's applying an inappropriate data format at an initial stage, so any changes i make are too late?

If it is indeed a JSON issue how can this be resolved? if not - any ideas?

Many Thanks

Andrew

[Anonymous]

Hi @Anonymous ,

Could you provide some error messages from like
if i change the format to text (string) or a different date format the data still comes through as an error.

and:

2022-05-16T00:00:00.0000000 - i've changed this to a more suitable format, or text but it still shows an error 

If your connection mode is Import, you may change the data type in Power Query after loading original data.

For more information, please refer to:

Data types in Power Query - Power Query | Microsoft Docs

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

[Anonymous]

Further searching has revealed this topic :

https://community.powerbi.com/t5/Power-Query/Power-Query-failed-to-parse-the-datetime/td-p/2389918

It appears that this is a bug in the CDM?

[Anonymous]

Hi,

Thanks for your response!

Error message from Power Query:

"DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
2022-05-16T00:00:00.0000000"

This is the same error whether the datatype is set as Date, Text, DateTimeTimezone etc.

I opened the query in FileSystem mode, rather than CDM and found the relevant JSON.

It shows dataFormat as "Date".

Thanks