I have a requirement to export all items to excel in a list which has exceeded the threshold limit. I've already tried using power query but since I have some null values in people picker fields in my list, it throws an error. Any suggestions? Thanks!
I've ran into this as well. I believe I ended up having to use the OData Connection in Excel pointed to ListData.svc to get around it.
You can use CSOM (client object). You can use the ListItemCollectionPosition class to implement paging list item retrieval according to the position of items relative to their collection. Use the RowLimit element to specify the number of items to return per page. You can create a Excel with the Items using OpenXML.