Excel Power Query is a great tool which allow my users to download SharePoint Online data into Excel tables and then perform self service business intelligence using Pivot Table, Chart and other powerful tool exposed by Excel client.
From SharePoint Online List
Power Query offers to you a connector named From SharePoint Online List which allows you to easily connect to SharePoint Online Lists.
It is very easy to use:
Type your SharePoint Online site collection url
Choose Organization account authentication type, signing in with your SharePoint Online credentials
And then click Connect.
A very user friendly user interface show the content of your site: you can select the list you want to export and then click on the Load button.
The Query Pane will show the progress of your download.
All is very simple to use, and, in one minute and some seconds I get 13.950 list items into an Excel Table.
Using the Query Editor, you can also perform a lot of data elaboration using visual command, but those are not a topic of this post.
Let’s to analyze what happen when I refresh my SharePoint Query: the connector performs a lot of calls to SharePoint REST API, getting data at chunk of using a lot of query like this
https://zsis01.sharepoint.com/sites/TestFlow/_api/Web/Lists(guid'46035a86-3be5-4756-a367-2ed8a96ffe1d')/Items?%24skiptoken=Paged%3dTRUE%26p_ID%3d12200
downloading a JSON string with 100 items for each call.
Now, after to have incensed this Power Query connector, I’ll start to quash it!
Well, this connector is fast enough if the list contains few field: when the field number increase, the performance decrease.
Another great limit is that I cannot perform server side filters: a customer of mine asked to me: “Why I have to download all list items if I need only 2017 items?”
My response was: “It’s by design”.
From OData Feed
Another way to download SharePoint data is to use the OData Feed connector.
In the OData Feed Url textbox, you have to type the Url of the REST Query in order to download SharePoint data using OData query, for example:
https://zsis01.sharepoint.com/sites/TestFlow/_api/web/lists/getbytitle('biglist')/items
Go to site collection named TestFlow, from the root web take a list name BigList and the give me all items.
This way does not have a user interface and you have to know the OData query syntax in order to query SharePoint list.
On the other hand, this is a very powerful query language that allow you to fine tuning what data you want to retrieve.
For example, you can set the paging size of the query: in my case I choose to download 2000 item for each call instead of the 100 items of the standard connector: the duration of my query is passed from 1’ and 24’’ to 35’’.
https://zsis01.sharepoint.com/sites/TestFlow/_api/web/lists/getbytitle('biglist')/items?$skiptoken=Paged=TRUE&$top=2000
Then… I want choose what fields I want to download: standard connector allows you to download all field and then, client side, hide the unwanted columns.
https://zsis01.sharepoint.com/sites/TestFlow/_api/web/lists/getbytitle('biglist')/items?$skiptoken=Paged=TRUE&$top=2000&$select=Title,MyDate
In this case, I take only 29’’ to download data and the JSON message is very slim
And, at the end, I can filter data server side, without downloading all records each time.
https://zsis01.sharepoint.com/sites/TestFlow/_api/web/lists/getbytitle('biglist')/items?$skiptoken=Paged=TRUE&$top=2000&$select=Title,MyDate &$filter=MyDate le '2017-01-01'
Be careful: if the list contains more then 5000 items, you must perform filter only on indexed fields, otherwise you will take back a 500 internal server error: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.
And so on, using any of the commands available in the Odata query language: Expand, Sort…
Summing: SharePoint Online Standard connector is very easy to use but has some performance and usability limits on list with a lot of items.
OData Feed connector require to know OData query language but is more powerful and allow you to perform a fine tuning of your query.
