Get SharePoint List Data with Power BI ... Fast

[@JoshJohnson-w1r]

Wanted to post my thanks; this solved my issue with a report that was timing out after a 2+ hour nightly refresh...doing everything through a web query brought that down to UNDER A MINUTE! You're a life saver, thank you!

[@EffnShaShinko]

This is pure gold. You saved me a ton of headache. Worked first time and did exactly what I wanted. Thank you so much!

[@richardwebb5447]

This was incredibly helpful to me. I also didn’t know about this functionality. Thank you!

[@SacheeT]

This is life changing and I successfully used this method with some modifications to the code and got a published to the service and using RelatedPath option by Chris Webb managed to do the automatic refresh configured too

[@SacheeT]

//I took the first ID and the last ID numbers and populate the skiplist table using those numbers, in 5000 chunks FirstId = Json.Document(Web.Contents(baseurl&"items?$top=1&$orderby=ID asc", [Headers=[Accept="application/json"]]))[value]{0}[ID], lastId = Json.Document(Web.Contents(baseurl&"items?$top=1&$orderby=ID desc", [Headers=[Accept="application/json"]]))[value]{0}[ID], skiplist = List.Numbers(FirstId-1, Number.RoundUp((lastId-FirstId)/5000), 5000), //Then using the relative path option custom table populated Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents( sitename, [ RelativePath = "/_api/web/lists/GetByTitle('" & listname & "')/" & "/items?$skiptoken=Paged=TRUE%26p_ID=" &[Skip] & fieldselect, Headers=[Accept="application/json"]]))), //Then just after expand remember to remove the duplicates of the ID, because when some records are deleted on the SharePoint List the same item will load manytimes with this method

[@csoulss]

@@SacheeT you both saved my life

[@Catarax28]

Thank you thank you thank you thank you!!!! Works like a charm, I had to replace the url string with "teams" instead of "sites", but got it working. Only got a duplicate row for every row after number 5000, trying to find the exact cause, but it seems the "top" function in the URL request isn't working

[@JustinFritts]

I know it's been a long time since you posted this but I believe changing these two lines will solve your issue. The original solution doesn't do well if there are deleted records. //itemcount = Json.Document(Web.Contents(baseurl&"ItemCount", [Headers=[Accept="application/json"]]))[value], lastId = Json.Document(Web.Contents(baseurl&"items?$top=1&$orderby=ID desc", [Headers=[Accept="application/json"]]))[value]{0}[ID], //skiplist = List.Numbers(0, Number.RoundUp(itemcount/5000), 5000), skiplist = List.Numbers(0, Number.RoundUp(lastId/5000), 5000),

[@Catarax28]

@@JustinFritts Thanx for replying!

[@mauvenegas94]

@@JustinFritts man thank you a lot, this was a life saver comment

[@hw1415]

Great article... except..... the PBI service doesn't appear to support scheduled refreshes using this approach. Am I just missing something? It's not very useful if it can't be refreshed in the service. PLEASE tell me I'm missing something here.

[@edwinjonah]

Wow, this is amazing! Thanks a lot for this, it will definitely save hours of work.

[@WilAleman]

Thank you very much for this video. Just moving to version 2 of the connector helped me improve the time for refreshing the reports.

[@abbashuzefamotiwala]

Thanks alot! it really helped!

[@ederpereira6326]

@Hoosier BI, I'm running into this error when refreshing online. This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Has anyone to fix this?

[@JohnBrines1969]

Excellent, many thanks. Have you done any videos on pulling the data from a SharePoint list (containing the same column names) that is on multiple site

[@imsteven3044]

I tried to deploy that in a data flow but it shows me can't save dataflow "one or more tables references a dynamic data source"

[@magnusgranberg1152]

Thanks a lot! Really helpt me. Saved approx >2 hours of loading time!

[@jordanhelus9598]

This has been very helpful! I do have a question about Multi Person columns from SharePoint Lists. How would you process this data in the MQuery?

[@corywarshaw4100]

The 2.0 is working well, except I can't get the items from subfolders. Does anyone know a way to get all items in the list when it has folders?

[@CmdrKeene]

Wow this is incredible and solves a very real problem.

[@juanantonioperez1727]

Great!!!!

[@raevanthkumar2879]

Hi All, This is a great workaround to get the share point list data very quick. I am trying to do schedule refresh , but I am not able to do so it says "data include dynamic sources". I know relative path would work but finding it difficult to implement on this custom code... Can someone please help me on this !

[@CairoMartins1]

Have you solved?

[@TrenerMS]

@@CairoMartins1 Has anyone figured out how to write it in this code?

[@mauvenegas94]

Same issue, anyone?

[@PMworkAPe]

Isn't the '_api/web' URI v1? In your video you say you're using v2, so I'm a little confused.

[@garantfilter3150]

Thanks a lot, very helpful.

[@sangtran4869]

Thank you very much! 🤩

[@CairoMartins1]

It works incredibly when using the desktop version, however I have ben having updating it when published. I have a dynamic data error which is not allowing me to get updates on the database. How can I fix it?

[@FamilyPlays22]

Firstly thanks, however, i can't expand type person columns values, how can do it?

[@JulianBrunner-r7p]

can I access the shared documents with that also? I am really struggling with this

[@zeroxzero159]

Just wondering can this method work with large lists where items are more than 5000? Can you retrieve all records if more than 5000 items?

[@rachelmarshall-centralboar3067]

Help - SharePoint list connector - how did you get that folder called SharePoint list connector and all the tables listed below the table.

[@laurentmarc1928]

Hi, this is great ! It almost does what I need : with your coe we obtain the user properties, and i'm stuck to obtain the user PROFILE properties ! Any recommendation?

[@legendgod]

I am using v2 connector to a spo list with 4500 items. It took 20 minutes to complete loading. It maybe because I have many columns. Can I reduce the number of column in query without using Json call?

[@shwetawagh1654]

I have the attachment files in my list, is that what making my refresh time longer? It’s taking 2 hrs approximately. Any leads would be much appreciated.

[@cameronbrown5480]

Thank you so much!

[@alexandreferraro3731]

May I could use for sharepoint folder/files too? I take a lot of minutes to load the query, huge sharepoint files... so frustating.

[@labcdel4384]

hey this was really helpful but how do you authenticate. Because normally with sharepoint connectors I can log in with my credentials and its fine. However with the JSON REST Api method it seems to reject that.

[@Lulu-wt8wk]

Hi I’m really suffering with my refreshing and applying changes part. My case is that I’m using a sharepoint list on premise, and not an online nor a subsite. So, how can I write the source string? Thanks,

[@Special_Excel_F_and_L]

My data have +50,000 records and is growing everyday. (I only need about 1200 records per query, but sharepoint is limited to 1000). I know I can filter the sharepoint query and now I know how to do pagination. But I can't figure out How can I use filter AND pagination ? everytime I try, I miss.

[@ryanroberts552]

Hi, I was able to get everything working and it is refreshing at an amazing speed! Unfortunately, I am running into this error when refreshing online. This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. I know its a long shot but if anyone can help with this I would be extremely grateful!

[@half_way_expert]

Try google Chris Webb's solution on RelativePath

[@CmdrKeene]

Found it!

[@sanupvarghese3330]

Were you able to resolve this issue? I tried Chris Webb's solution but for the codes used in this video its not working fine. Let me know if some one can help how to use Relative Path in the Query codes in this video

[@CmdrKeene]

@@sanupvarghese3330 I got it to work for me and it's been a lifesaver. I use it to get SharePoint data into Excel or PowerBi with PowerQuery, retrieving data from a SharePoint list that has nearly 150,000 records in it so far. And the query refreshes in about 15 seconds, it's great. I'd be happy to share the query with you. Shoot me an email from cmdrkeene.com

[@sanupvarghese3330]

@@CmdrKeene It is life saver for sure. But only problem I am facing is in PowerBI service Auto Refresh. I tried using the Relative path but not able to get it right in the code given. I am not too familiar with coding.

[@johnangelomanzi]

Hi, this is a great find. Never knew power query could handle odata like this. I'm really missing the filtering clause though. Any references for a "Where" clause?

[@hoosierbi5669]

You'd do a WHERE clause with a $filter parameter. It's a little tricky though and needs to be done on an indexed column.

[@hoges510]

I keep getting a unspected character in JSON input Value =< and Postion =0

[@diegopaz83]

Thank you

[@deuts]

How can I apply this to a Document Library instead of a list?

[@simmly2005]

did you get an answer for this?

[@freddyparedes6917]

I'm sticking with this error very good video, but for famor you could help me get the name of created by and modified by ? do not appear to me

[@freddyparedes6917]

I think you are the only one who explains these problems with sharepoint in powerbi you are a crack greetings from Spain

[@joeyd5404]

Post notifications😼😼

[@lalit3153]

@Hoosier BI, I'm running into this error when refreshing online. This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Has anyone to fix this?