Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Parse error querying SharePoint Online list using 2.0 Implementation

I'm getting I'm getting an error in date/time fields with SharePoint Online list, this is when using the 2.0 (Beta) implementation.

Note: I don't have this issue when using the 1.0 implementation (which is a bit slower).

The error mesage I'm getting is:

DataFormat.Error: We couldn't parse the input provided as a DateTime value.

Details:

16/11/2020 18:04

I've tried changing the locale (to English UK), but no luck so far.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Today, I refreshed my query, and the issue has magically fixed itself.
    I believe this is an issue caused by my locale settings: I checked the following

    1. Workstation: I had reset them all to English (Canada) and specified the Long Date format to be dd MMM yyyy. My workstation has also gone through a shutdown and restart cycle.
    2. M365: I've also ensured my M365 language and regional settings are set to English (UK)
    3. SPO site (datasource): Set the Regional Settings to ensure English (UK) is also set (I have Site collection admin rights to the site)

     

7 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Add a Replace Values step on that column replacing the space with a "T" before you try to change to DateTime.

    Regards,

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Pat,

      Unfortunately, that doesn't work. Even before I can change the type, Power Query is already determining the column type - it already knows it is a Date/Time type.

      Here is a screenshot in PowerQuery right after I've selected the list (in the Navigation step). I haven't had a chance to change the type yet.

      I do wonder if it's something to do with the locale - as some dates do make it through (if either month or date is 12 or less) - but I have already forced the locale to be en-GB (English-UK)

       

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Could you provide an image of the dates so that we are able to see if there are any issues directly.

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Liang,

      Here you go:

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Today, I refreshed my query, and the issue has magically fixed itself.
    I believe this is an issue caused by my locale settings: I checked the following

    1. Workstation: I had reset them all to English (Canada) and specified the Long Date format to be dd MMM yyyy. My workstation has also gone through a shutdown and restart cycle.
    2. M365: I've also ensured my M365 language and regional settings are set to English (UK)
    3. SPO site (datasource): Set the Regional Settings to ensure English (UK) is also set (I have Site collection admin rights to the site)

     

  • talk2frank's avatar
    talk2frank
    Icon for Helper I rankHelper I
    1 year ago

    sasme problem but my system is full uk locale and still wont do anything but fail to parse and shows an error

     

  • talk2frank's avatar
    talk2frank
    Icon for Helper I rankHelper I
    1 year ago

    none of this works for me sadly, its just broken as the parse is hidden in the dataload rather than allowing me to manage it myself