Forum Discussion
Advocate IIsharepoint view
Community Championgbritton - You don't. When Power BI looks at a SharePoint list, it is not looking at a particular view, it is looking at the raw list and seeing all of its various fields, even ones that are typically completely hidden in SharePoint. It is more like when you look at list from a PowerShell perspective. Basically, the way it is accessed bypasses a lot of the "SharePointiness" that goes on (read, the platform saving you from yourself). Think of it as the regedit for SharePoint lists only without the edit and no registry. In any event, to "recreate" a particular view, start with everything and then remove the columns you do not want.
Greg_Deckler That's what I suspected too. But the SP list I need to use has more than 10,000 items. By the ordinary method, Power BI, Power Query or Access can't connect to the list because it's over the 5000-item limit.
After I exported the list, I got the following query in a iqy file:
WEB1https://xxxyyyzzz.com/app/admin-review/_vti_bin/owssvr.dll?XMLDATA=1&
List={B53D6D9C-AC94-4DE7-8A5D-EF35ED42AAA7}&
View={ED21F1FF-8057-4058-9E56-F6DF6145C990}&
RowLimit=0&
RootFolder=
Selection={B53D6D9C-AC94-4DE7-8A5D-EF35ED42AAA7}-{ED21F1FF-8057-4058-9E56-F6DF6145C990}
EditWebPage=Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=https://xxxyyyzzz.com/app/admin-review/_vti_bin
SharePointListView={ED21F1FF-8057-4058-9E56-F6DF6145C990}
SharePointListName={B53D6D9C-AC94-4DE7-8A5D-EF35ED42AAA7}
RootFolder=
Connecting to B53D6D9C-AC94-4DE7-8A5D-EF35ED42AAA7 (the list) in Power BI, I got an error message of "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator". But Power BI can't find a table if I change the list name to ED21F1FF-8057-4058-9E56-F6DF6145C990 (the view).
Much appreciated if you could help me to get around with this issue.
- Greg_Deckler
Perhaps try the technique here:
Alternatively, bump up your list view threshhold.
Thank you, Greg_Deckler. It seems the threshold is not imposed by administratior. Rather it's a hard limit on SP lists that are not indexed.
But after my post, I think I have found a way to work around it, which could also be the solution for the original question of this thread gbritton. It turns out Power BI can also connect to SP list through "Web" under "Other" in Get Data. The steps are as below:
1. Go to the view of a SP list, export it and save the iqy file, instead of opening it straight away.
2. Open the iqy file with Notepad and copy the URL address, which includes names of list and view.
3. Get Data in Power BI by choosing Web.
4. Paste the URL link in the field and choose the right table
5. Repeat the steps above for other views and join all the tables together to get the whole data.
I am curious to know how you got this to work as I still receive the error about maxed lookup limit. Do you have screen prints that you can share?
