sharepoint view

[gbritton]

I want to import data from a SharePoint List into Power BI Desktop.  However, the list has several views available.  How do I specify which View I want?

[Anonymous]

Hi gbritton,

Have you resolved your pblm? i have also same requirement

Thanks!!

Nagaraju

[Anonymous]

Here is a workaround.

https://community.powerbi.com/t5/Power-Query/sharepoint-view/m-p/313306/highlight/true#M14385

[Anonymous]

Hi Guo_au

Thanks for the reply

i am getting the error while following that work around.

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=

in the above list in the view iqy quary up to what portion i need to take for the web connection.

can you please give me some sample view url for the web connection in power bi

Thanks!!

Nagaraju

[Anonymous]

Try again without "WEB1" in the URL. My current work environment does not have SharePoint. 

[Anonymous]

Hi Guo,

Thanks for the quick reply

i am using the below entaire url still getting the error

https://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=

 and i tried to reduced that url upto list id and view id below is the link but still getting the error

https://xxxyyyzzz.com/app/admin-review/_vti_bin/owssvr.dll?XMLDATA=1& List={B53D6D9C-AC94-4DE7-8A5D-EF35ED42AAA7}& View={ED21F1FF-8057-4058-9E56-F6DF6145C990}

I know currently your not useing share point connection,but if you rember any thing please reply me ,it would be helpful for us

Thanks!!

Nagaraju.G

[Greg_Deckler]

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

[Anonymous]

The list for a given SharePoint does not hold any Lookup column values which makes it difficult to pull them onto PowerBI. 

[Anonymous]

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

https://social.technet.microsoft.com/Forums/en-US/edde2e31-2069-4d89-841d-68d081d03c4c/ability-to-ex...

Alternatively, bump up your list view threshhold.

[Anonymous]

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.

[isabellamai]

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?

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

@gbritton - Calculated columns should come through as whatever value they have at the time of grabbing the list. I'm going to check this one so stay tuned.

Columns coming from other data sources (I assume Lookup columns here) often do not need anything special done. At worst, you have to click the little arrow icons in the column heading to expand the column to the particular value you are looking for.

Filters most definitely have to be recreated during the query or you pull in all the data and filter it in the report (Page filter, Report filter, etc.) In the query, you click the little down arrow in the column heading and choose the values you want to include and uncheck the ones that you do not.

[gbritton]

I'm no SP expert, (Far, far from it!  I'm a SQL guy) but I thought that a caclulated column could be defined for an SP List view that is *not* in the raw, underlying list.  In other words, PowerBI won't even see the computed column since its not in the raw list.  Is that not the case?

[gbritton]

Check out all the customization options here.  I want to define them once (in the SP view) but use them everywhere.

[Greg_Deckler]

@gbritton - Calculated columns do persist in the underlying list as a column with a value. They are "hidden" from forms by default in SharePoint.

I am very familiar with all of the various customization options for SharePoint Views. The link that you sent doesn't even cover half of what is possible just in tweaking the default view settings. I think that the underlying theme here is that the vast majority of those list options are user experience things and there are so many of them that not all of them would translate to a data ingest process. SQL views by contrast are very limited in what they can do and they essentially come out as a table. Not so with SharePoint, views can look and behave radically differently. Thus, I can see how it becomes problematic trying to support all of those different UI switches in SharePoint views. I'd submit it to Ideas. I think it has merit as something explore.

One note, Lookup fields can be tricky, they store the ID of the item from the other list and if you display other related fields from the lookup list, those are NOT persisted in the SharePoint list, so if you want them, you have to pull in the other list as well and relate them.

[yatinpurohit]

I prefer using SharePoint Designer workflow instead of calculated columns. You cannot use calculated columns for lookup, in PowerBI, PowerApps or any other purpose than displaying the calculated value in a SharePoint list view.

[gbritton]

Good Idea. I filed one here