Forum Discussion

hjaf's avatar
hjaf
Advocate I
6 years ago
Solved

SharePoint list query alternative or optimization

Hello everyone!   Because sharepoint list queries quickly comes too slow to work with in Power BI, I have experimented with the query that sharepoint generates for excel. This is vastly more effect...
Need Help
Tips and Tricks
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    hjaf FYI that I finally made a video to describe this approach, and am adding it here for others that may find this post.  It also gets the count of items and makes the right number of API calls.

    Get SharePoint List Data with Power BI ... Fast - YouTube

     

    Also, a reminder to mark one of these as the solution.

     

    Regards,

    Pat

Richard_VTR's avatar
Richard_VTR
New Member
5 years ago

Hi mahoneypat ... I am using your approach to get the items using REST API and it works really fast. But I have published the file to the power bi service and I cannot set the schedule refresh because I have this error: "You can't schedule refresh for this dataset because the following data sources currently don't support refresh".

 

Do you know how to overcome this problem?

 

Thanks

Saide's avatar
Saide
Frequent Visitor
11 months ago

This fixed it for me. It was not refreshing as PBI App was recognising it as dynamid data source. Replaced Custom1 step with this: Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(baseurl, [RelativePath = "/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect, Headers=[Accept="application/json"]]))), 

It works all right for me