I have a number of different Excel files which connect to a number of different SharePoint folders for a variety of purposes. Each week, new data is exported from our client systems and saved in these folders. Then, the Excel files are refreshed to include the new data. The metrics we get from these PowerQueries are critical to our operations.
This has been running smoothly for around 6 months until we wanted to update this week. The files were saved in the same folders as always, but when refreshing the file, nothing happened - it does not register the new data, only the old data.
Upon further inspection, I can see that while the authentication to SharePoint works well, the newest file on our SharePoint site (based on "Date created") is from December 12 - and I am writing this on December 20.
From testing a bit more, it seems that all files where the file creation date is later than December 12 are simply not visible to PowerQuery. This strange bug does not impact file modifications, i.e. modifications I make to files added to SharePoint before December 12 are captured just fine and the modification date is updated. This rules out that the preview is not refreshing at all - it is simply not taking in new files.
What I have tried
- Rebooting my PC
- Waiting a day or two to see if anything changed
- Refreshing from another pc
- Refreshing from another network
- Connecting to a SharePoint folder via a brand new Excel file
- Connecting from PowerBI Desktop instead of Excel PowerQuery
- Reauthenticating my Microsoft account
- Adding arbitrary files to arbitrary folders - they still didn't show up
- Asking our IT department if they changed my permissions or in any way changed the SharePoint server recently - they did not
Details that might be helpful
- We are working in multiple time zones (UTC-6, UTC, UTC+1)
- The files are .csv
- I am on version 1808 build 10730.20262, 64bit with a Microsoft Office 365 ProPlus
- We were recently acquired, so we got new email addresses with different domains. We still use our old emails for SharePoint authentication, however. We have not lost access to these accounts and I can access Office 365 just fine.
- The issue affects files added after December 12. I see that PowerQuery has problems parsing the dates from SharePoint as they are in mm/dd format and PQ tries to interpret them as dd/mm. This causes all dates where the day value is higher than 12 (i.e. higher than December if it was a month) to throw an error. Since I am not applying any logic to these date values and since it has never been a problem in the past, I have not reacted on it.