top of page
Writer's pictureOscar Martinez

How to Use SharePoint OData in Power Query

Updated: Nov 13


How to Use SharePoint OData in Power Query

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
    Source

2. 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
    Source

Consider 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.



Helpful links to Microsoft documentation


1,981 views

Related Posts

See All

2 Komentar


Tamu
27 Mar 2022

How do we get the sharepoint files binaries with odata?

Suka
Oscar Martinez
Oscar Martinez
28 Mar 2022
Suka
bottom of page