Forum Discussion
Unexpected results for data source SharePoint 2013 List items using a SharePoint REST API
I am trying to pull items/properties from a SharePoint 2013 List using a REST API call. I cannot use the Power BI built in data source because over 8 GB of data is getting returned because of PDFs being part of the SP list. I would like to use the REST API call to limit which fields that are returned.
I came across this article https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest
And ended up with a Source like
Web.Contents("https://mysite.org/dept/sites/mysite/_api/Web/Lists/GetByTitle('mylist')/items?$select=*")
I have tried to parse the results as Json or XML or even CSV but the results do not make sense for what I expect. I do get results I just can't make sense of them.
What am I missing?
Thanks in advance, Scott
You shouldn't be using REST, you should use OData. If you set your filters in Power Query as shown in this link, https://marqueeinsights.com/how-to-data-mine-a-sharepoint-list-with-power-bi/ they will be automatically added to the OData call and filtered out. The process for SharePoint 2013-2019 and Office 365 are the same.
Hope this helps!
--Treb, Power BI MVP
1 Reply
You shouldn't be using REST, you should use OData. If you set your filters in Power Query as shown in this link, https://marqueeinsights.com/how-to-data-mine-a-sharepoint-list-with-power-bi/ they will be automatically added to the OData call and filtered out. The process for SharePoint 2013-2019 and Office 365 are the same.
Hope this helps!
--Treb, Power BI MVP