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.
gbritton
@smoupre wrote:gbritton - 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.
I was afraid of that! It's actually quite unacceptable. There are some fields that are in constant flux in one list I access -- breaking any work that accesses that (raw) list. However there are views defined over fields that the business has agreed to leave as is. Those views should always work, no matter what happens to unrelated fields.
- Greg_Deckler
gbritton - Can you provide a specific example that explains the use case and why you can't just essentially recreate the views in your queries by selecting the same fields that those views use?
Here is what I don't understand. You have a view defined over certain fields, let's call them FieldA, FieldB, FieldC. In your query, to to the SharePoint list and select FieldA, FieldB and FieldC and then select "Remove Other Columns". Now you have a query that only has the same fields as the view.
Please explain what I am missing.
- gbritton
That check box is new to me. I'll try it.
However"
What about computed columns in a list view or columns coming from other sources etc or filtered view?
Sounds like this has to be redone in PBI. then somehow kept in sync with the list view. Nasty!
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.
The list for a given SharePoint does not hold any Lookup column values which makes it difficult to pull them onto PowerBI.