Learn best practices for using SharePoint OData in Power Query to streamline data imports and overcome common challenges.
I initially used SharePoint OData in Power Query to connect my SharePoint data to Power BI. It worked fine until I encountered lists with over 20K elements, where I ran into challenges. Here’s what I learned and why I now recommend using the SharePoint API connector instead.
Challenges with the OData Connector
Using SharePoint OData in Power Query has limitations, particularly with tricky pagination in large data sets. For large lists, there’s no “next link” data or reliable way to call the next set of items starting from element “X.” I couldn’t get pagination to work reliably, which became a roadblock.
Why I Recommend the SharePoint API Connector
Switching from SharePoint OData in Power Query to the SharePoint API connector has been a game-changer for larger data lists. The API handles pagination smoothly and performs better with substantial datasets.
However, if your list is under 20K items and you only need specific columns, the OData connector still has some advantages:
Speed: OData is faster for small lists.
Column Flexibility: OData lets you retrieve only the columns you need, whereas SharePoint connector V1 retrieves all, and V2 requires a view setup.
Sample Code for OData Connection
For those working with smaller lists or exploring options, here’s some sample code to connect with OData and request specific columns. Remember, column names are case-sensitive:
1. Connect to the list via the OData connector.
Connecting to the SharePoint list via the OData connector without specifying any column will result in a table with all the list columns (similar to the SharePoint connector v1).
let
Source = OData.Feed("{SharePoint Base URL}/_api/web/lists/getbytitle('{List Name}')/items?"
,null, [Implementation="2.0", Headers = [Accept = "application/json"]])
in
Source2. Request specific columns.
Using parameters in the URL, you can specify the columns that need to be retrieved.
let
Source = OData.Feed("{SharePoint Base URL}/_api/web/lists/getbytitle('{List Name}')/items?"
&"&$select=Column1,Column2")
in
SourceConsider that you need to use the "Internal name" of a column; for getting the internal names of columns, you can use the Gist below:
3. Request specific expanded fields.
When requesting fields that need to be expanded, you need to specify the column to be expanded and the expanded field in the "Select" parameter and the column to be expanded in the "Expand" parameter.
"&$select=Column3/ExpandedField1,Column4/ExpandedField2"
&"&$expand=Column3,Column4"4. OData service endpoint in SharePoint
We can also make use of the listdata.svc OData feed for SharePoint lists and libraries, which works smoothly with the SharePoint OData URI conventions.