Forum Discussion

achates's avatar
achates
Frequent Visitor
4 years ago
Solved

Power Query failed to parse the datetime

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 ...
Need Help
  • achates's avatar
    achates
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Try transforming the columns instead of changing the column type:

 

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

  • achates's avatar
    achates
    Frequent Visitor
    4 years ago

    Hi watkinnc, I tried, but same error.

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Icon for Most Valuable Professional rankMost Valuable Professional
      4 years ago

      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's avatar
        achates
        Frequent Visitor
        4 years ago

        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.