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?

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.

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

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.

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!

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.

sharepoint view | Microsoft Fabric Community

20 Replies

Greg_Deckler
Community Champion
10 years ago
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.
- gbritton
  Advocate II
  10 years ago
  @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
    Community Champion
    10 years ago
    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.
- Anonymous
  Not applicable
  8 years ago
  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
    Community Champion
    8 years ago
    Perhaps try the technique here:
    
    https://social.technet.microsoft.com/Forums/en-US/edde2e31-2069-4d89-841d-68d081d03c4c/ability-to-export-the-items-to-excel-of-more-than-5000-items?forum=sharepointgeneralprevious
    
    Alternatively, bump up your list view threshhold.
- Anonymous
  Not applicable
  6 years ago
  The list for a given SharePoint does not hold any Lookup column values which makes it difficult to pull them onto PowerBI.

Anonymous

Not applicable

6 years ago

Hi gbritton,

Have you resolved your pblm? i have also same requirement

Thanks!!

Nagaraju

Anonymous

Not applicable

6 years ago

Here is a workaround.

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

Anonymous

Not applicable

6 years ago

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

Forum Discussion

sharepoint view

20 Replies

Related content

Sharepoint list mirroring

Viewing/Editing of SQL Views

Understanding Lineage View

SharePoint Access

Extract Data from sharepoint