Sharepoint to Excel Power Query is suddenly returning a fraction of my data

Question

Office 365. Excel Version 2304 (Build 16327.20248) & SharePoint Online

I created Power Query, using Get Data, From Online Services to pull data from a SharePoint library. Yesterday, it returned thousands of rows of data. I set it to refresh every 10 minutes and when Refresh All is selected. It was working fine yesterday. This morning, my dashboard which is fed by the Excel file was broken. I opened the workbook to find that only 172 rows were present. Nothing changed in the query and nothing changed in the library. I've refreshed the query repeatedly. I've even tried opening a new workbook and rebuilding the query from scratch. Any help to troubleshoot this issue is appreciated.

Answer 1

From you descirption, issue should be locate in Sharepoint side. You may raise a support ticket to Microsoft.

Please contact your admin/IT department create a support ticket via Microsoft 365 Admin Center> Support> New service request. That support team there will have the correct channel and resources to help you investigate more and find what exactly the reason has caused this situation.

Contact Us - Microsoft Support

Answer 2

I'm not sure what you want me to check. At this point, I've messed up the query that was working a couple of days ago.

Background. In SPO, I've created a custom content type called Sales Document, which is a folder with specific metadata fields. In my library, I have 172 regular folders and 31 Sales Document folders for a total of 203 items that show up in the list view of my library. I have a column called Content Type that allows me to filter the list to see only Sales Documents. Frankly, that is all that I want to load. Both the folders and Sales Document folders contain subfolders and files, but I don't need to load any of those items.

I've tried to recreate the query using a variety of selections on the Get Data > From Online Services > From a SharePoint List:

• If I select 1.0 for the Implementation options, I get 13K rows which include, every folder, Sales Document, and file within the library. However, I don't get all the columns, specifically, I do not get my Content Type column that allows me to filter the 13K down to show me only the Sales Document folders. I also don't get my Disposition column which is specifically what I'm trying to create a dashboard for.
• If I select 2.0, I get all the columns in my library, including Content Type and Disposition, but only 172 of 203 list items load. There are 3 of my 31 Sales Documents. Interestingly, they are all items that were uploaded as regular folders, after which I applied the Sales Document content type. But all the Sales Documents that have been created within SPO are not returned.

I don't know if any of this helps.

The other oddity is that it all worked two days ago. I set up the query using the 2.0 implementation and selected the All columns in Library option. When I originally did that, I got all my columns and 13k rows. I then created counts of sales document disposition to create a bar chart of Sales Documents by Disposition. I then embedded the charts in SPO via the iFrame feature. It was all working. Then the next day, the query results went from 13K rows to 172 rows and all my charts broke. I have not been able to recreate a query that returns all the rows and columns for the Sales Documents library. So frustrating.

I think it's downright stupid that I have to go through all this to create a bar chart based to show items counts. I have views that show counts, but there is no webpart that will allow me to build a chart based on counts. I just want my folks to be able to see how many sales documents are in-progress, lost, won, etc.

Answer 3

Could you check the file source steps in power query? It could be something changed in SPO side.