SharePoint OData in Power Query

Recently I've worked a lot with SharePoint data in Power BI, my first approach was using the OData connector, this works relatively fine until you have a list with over 20K elements. The SharePoint OData connector can't manage pagination, as the parameters required to make a new call starting on "X" element do not work.

Today, I no longer recommend the OData connector, on its place I've been using the API SharePoint connector which allows for pagination.

Still, if you are looking into getting SharePoint data into Power BI via de OData connector below, below you will find the sample code, be careful when you input the column names as these are case sensitive

1. Reasons for using OData connector instead of SharePoint connector.

- OData is quicker to retrieve data from SharePoint

- Your list will not go over 20 thousand elements.

- With OData, you can specify only the needed columns. SharePoint connector V1 will retrieve all the columns from the list, including the system/hidden ones; v2, can request the default view, but you probably want to use some of the hidden/system columns.

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

3. Request specific columns.

Via 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:

4. Request specific expanded fields.

For requesting fields that need to be expanded, you need to specify the column to be expanded and expanded field in the "Select" parameter as well as the column to be expanded in the "Expand" parameter

"&$select=Column3/ExpandedField1,Column4/ExpandedField2"
 &"&$expand=Column3,Column4"

Want to know more?

Links to Microsoft documentation:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/get-to-know-the-sharepoint-rest-service?tabs=csom