How to use an OData Query to Pull in SharePoint List data?

[jkball04]

I have a use case where I need to pull in a SharePoint list into PBI, but because of the number of lookup columns clients have created, cannot load the data through the typical "SharePoint Online List" connector.

I've read online others have had success in using OData queries to return SharePoint List results, but when I create the query it prompts me for my creds, I provide, but it will not authenticate my creds. I am the creator of the site and have full control. The settings in Power BI has been updated to use my default browser for authentication.

I've had success in using an OData query to query ADO data, but no luck so far with SharePoint List data. Anyone been successful in bringing in SP data this way? If so, can you please provide your query for reference? Ty!

[hnguy71]

Hi @jkball04 ,

Odata from SPO or ADO should be close on implementation. Here's an example for SPO List:

OData.Feed("https://sharepoint.com/sites/MY_SITE/_api/web/lists/GetByTitle('List Name')/Items?$select=Title,TaskDetail,Task_Status,Actual_Effort,Planned_Effort,Month,Year,Recurrence,WorkDayType,Work_Day,MonthDay,Team,Due_Date,Responsible/Title&$expand=Responsible/Id")