Unexpected results for data source SharePoint 2013 List items using a SharePoint REST API

[wskallmeyer]

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

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

[trebgatte · Accepted solution]

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

https://getstartedwithpowerbi.com