Power Query failed to parse the datetime

[achates]

I am importing data from data lake gen2 with PQ and having error with some columns as below. Data in column should be in time zone format. I tried to convert it to text, datetime, datatimezone using locale, etc., but all have same error. How can I fix this issue?

Data is being imported as Common Data Model, source data is saved in CSV format.

BTW, before I can convert the type, it was in Error in last Navigation step already. This means I have to convert a column in Error status. Will this be a problem? However, there is no way to show the column in any type that has no Error.

[mahoneypat]

From the error message, your values are DateTime, not DateTimeZone. If needed, you can use Date.Time.AddZone to add the timezone after you convert it to DateTime.

Pat

[Anonymous]

Actually, you might be better off using DateTimeZone.FromText, and using the optional formatting record [Format="dd MM yyyy HH:mm:ss.ffffff zzz", Culture="de-DE"])

--Nate

[Anonymous]

Try transforming the columns instead of changing the column type:

= Table.TransformColumns(#"Navigation 6", {{"ActivatedDate", DateTimeZone.From}})

[achates]

Hi watkinnc, I tried, but same error.

[Vijay_A_Verma]

Hope there is no non-printable character in that. You can try cleaning it up through Transform - Format - Clean.

If there is still an issue, I would recommend if you can upload a file containing few rows to Onedrive / other file hosts without confidential data and share the link here. This will help volunteers here to provide right solution. 

[achates · Accepted solution]

Hi Vijay, According to my further testing, it sounds like a bug of Common Data Model.
When I import data with CDM, certain datatime column will be treated as Error in Navigation step. I don't even have any chance to do any transformation to fix it. If I import with file mode, the column will be firstly detected as Text, then I can convert it to timezone by using locale without any problem.