Date bug with Sharepoint file sources

[Sascha1]

As mentioned in these threads:

- http://community.powerbi.com/t5/Integrations-with-Files-and/Date-issues-with-Sharepoint-file-sources...

- https://community.powerbi.com/t5/Desktop/Sharepoint-folder-date-problem/m-p/382489/highlight/false#M...

PowerBI has a bug since the March release when it comes to reading files from Sharepoint and using the date fields provided by Sharepoint. This not only affects the desktop but also the service.

[eFeM135]

Going back to API Version 14 seems to be the most selected work-around, which is odd as one day this version will become obsolete causing mayor issues.

I'm finding data typing in Power Query extremely important, and believe this issue with API Version 15 is exactly that: we expect a Date/Time/Timezone field in an unified timestamp format that we can then use in a global context, but the API reads a Date/Time field using the locale of the sharepoint site and returns it using the locale of the Power BI (or Power Query) user leading to errors when going from mm/dd/yyyy to dd/mm/yyyy (21st of February 2020 on sharepoint reads as 02/21/2020 and is returned to the local Power application in the dd/mm/yyyy local where it fails as month 21 is invalid, luckely the 8th of February 2020 does not return an error and can be correctly displayed as 08/02/2020 on local).

When passing the date/time issue, we still need to handle the timeshift as a result of the difference in timezone between the sharepoint location and the Power user. 

My first work-around always is to try to have the same regional settings on the sharepoint and the Power community using these sharepoints... so when your company is based in Brazil, most European countries and mainly uses dd/mm/yyyy, ask your sharepoint adminstrators to set the Locale and Timezone in (Site Settings / Site Administration / Regional Settings ) to the company default.

Of course, through voting we should remind Microsoft Power team that they need to ensure they think Global/Cloud rather than Local/Office in every aspect and work with timestamps which can be handled both at Cloud and Local level.

Not sure whether we will get an API Version 16 return a timestamp in universal format!

[fenixen]

How has this not been solved yet 😞 

Norway uses English and Norwegian 50/50 on software in business environments, we cannot force either. Microsoft has to make the timestamp universal! 😞 

When I change to API version 14 i dont get any data at all. 

[Burubear]

HI guys,

Still getting the same issue as of today. I can't also use the "Changing to API 14" as a solution since when i can't get the sharepoint list that I want when I changed it. And as eFeM135 mentioned, this version would be obsulete moving forward so using this a workaround would be trouble in th future.

For my issue, it's not just in a matter of changing the hrs. All of my dates are 1 day behind and when changing into the date and time, i noticed that PBI imported all of the data into a standard 12:00am time so changing to locale wasn't the solution. Hoepfully there's another workaround to this

[lbendlin]

The issue is back in the April 2020 desktop. Workaround is still either to downgrade SharePoint.Files to ApiVersion = 14 , or to use SharePoint.Contents instead (with extra steps required in Power Query)

[fenixen]

Thanks @lbendlin , using Sharepoint.Contents works ! 🙂 
Are there any downsides we should know about? 

[Anonymous]

I'm trying to deal with this issue but there seems to be no ideal solution: working with [ApiVerion = 15] my query doesn't fail but the date fields are misinterpreted (US -> UK).

With  [ApiVerion = 14] dates look fine, but when I create a step which incorporates all records (e.g. sorting) the query fails with following error:

DataSource.Error: SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error. (An error occurred while processing this request.)

It's quite ironic that the two Microsoft products can't communicate effectively.

[fenixen]

@Anonymous  try using sharepoint.contents instead of files/folder as described earlier in this thread. Its a bit more work navigating to the correct folder but I've used it in several workbooks the last 6 months. 

[AlexanderJ]

I have created the following function to solve the issue for myself.

It should be invoked as fixSharepointFiles(Sharepoint.Files(Site-URL, [ApiVersion = 15]))

It uses the try keyword to get to the source string and then handles the original string under the assumption that it is in the en-US locale.

If the automatic conversion did not error out, it's because Power Query assumed that the day was the month and vice versa, so I switch them back. 

It seems to work without a problem for me but you may need to correct for the timezone of the Sharepoint server. 

let fixSharepointFiles = (Sourcetable as table ) as table =>
    let 
        fixSharepointDates = (cellRecord as record) as nullable datetime  =>   
        let              

            result = //Error => use US locale   
                    if cellRecord[HasError] then DateTime.FromText(cellRecord[Error][Detail], "en-US")
                    //null stays null
                    else if cellRecord[Value] = null then null
                    //if a valid DateTime was recognized, PQ switched month and day, so we switch it back
                    else 
                        //store current value in Record
                        let datetimeRecord = DateTime.ToRecord(cellRecord[Value]),
                            //create new datetime with correct order
                            fixedDate = #datetime(datetimeRecord[Year],datetimeRecord[Day],datetimeRecord[Month],datetimeRecord[Hour], datetimeRecord[Minute], datetimeRecord[Second]) 
                        in fixedDate
        in  
            result,
        #"Add DateRecords" = Table.AddColumn(Sourcetable, "DateRecords", each [#"Date accessed" = (try [Date accessed]), #"Date modified" = (try [Date modified]), #"Date created" = (try [Date created])]),
        #"Remove old date columns" = Table.RemoveColumns(#"Add DateRecords",{"Date accessed", "Date modified", "Date created"}),
        #"Expand DateRecords" = Table.ExpandRecordColumn(#"Remove old date columns", "DateRecords", {"Date accessed", "Date modified", "Date created"}, {"Date accessed", "Date modified", "Date created"}),
        #"Process and transform date records" = Table.TransformColumns(#"Expand DateRecords", {{"Date accessed", fixSharepointDates},{"Date modified", fixSharepointDates},{"Date created", fixSharepointDates}})
    in
        #"Process and transform date records"
in
fixSharepointFiles