Forum Discussion
Power Query not bring all the records across from oData source
I have about 20 queries grabbing data from an oData source. One of them is fetching from a source with about 2500 records in it, but for some reaosn seems to randomly stop at about 1938.
There is pagination involved, but other queries in the same set have over 3000 records, and they are all coming through fine, using exactly the same m code (just a change of source). I've checked on PostMan and the records exist as far as the api is concerned.
It's not a caching issue, everything else seems to be updating okay.
The most succesful experiemnt I tried was filtering the url to see if could force the queery to bring back the missing records. I filtered it to anything with a creaed date after 10/10. And it came up with an error. If I change that to after 01/10, it works. I fiddled around a bit, and found that I can filter to 4/10, and it will still work, showing records I've created on 21/10. Without that fiddling, it gets as far as 18/10.
But I don't think it's anything to do with the date, because it just stops returning records halfway thruogh a batch on one date, and I can't work out what has changed about the data from one row to the next. And I dont' think it's to do with the number of rows, because it's paginated in 200s, but stops on a random number.
I know this is a long shot, because there is probably a million and one things that could be wrong. Just hoping that someone may have seen an issue like this before and have some sensible troubleshooting suggestions.
Any help would be very much appreciated. I've been banging my head against a brickwall, and was so close to getting all my data sorted and building some reports, before I noticed these 500 records were missing. And it's a deal breaker if I can' get to the bottom of it.
1 Reply
- Sahir_Maharaj
Super User
Hello tigertedd,
I would recommend extending the timeout duration in Power Query’s options (Data Load > Query Execution).
Larger datasets can exceed default timeouts, especially if the data size grows close to the limit.
It also might be worthwhile to start a diagnostic session, refresh the query, and review the logs to see where the query breaks or stalls. This might highlight issues around specific records or response handling. (Tools > Diagnostics)
Hope this helps.