cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wskallmeyer
Advocate II
Advocate II

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

1 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

View solution in original post

1 REPLY 1

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.