Gerald Britton on 19 Mar 2016 00:46:58
PowerBI supports pulling data from SharePoint lists but only from the default view. My business has tons of views set up. We need to use those views directly in PowerBI
- Comments (6)
RE: Support SharePoint List Views
I found a workaround using the JQY query url generated by sharepoint when you "Export to Excel" from sharepoint and using that in the web connector. Works find for site collections when I have Admin Rights but won't work when I try to use lists/views in collections that I only have full admin permissions to access. I can usually get it work using the SP List Connector or a JSON query but each of these require significant manipulation to transform the data. When using the JQY query url in the Web connector it very easily imports just the details for the view I'm looking at including any calculated columns, values for person records etc..
The implementation within Excel works great and I can use the resulting table as a data source, however when trying a powerquery solution I get the permissions errors unless I have permsissions to access the VTI_BIN SP Application folder.
RE: Support SharePoint List Views
Agreed, also the view could be filtered on an indexed column so that Power BI gets only the requested under 5k list items where the full list might hold 50k o rmore. And the view can exclude the calculated columns and excess workflow columns that hit other restrictions.
RE: Support SharePoint List Views
Additionally this would let folks deal with too many lookup columns, or calcuated columns, which are currently choke points for Power BI from sharepoint lists. Likewise the view could be filtered to under 5k rows for access to only relevent portions of the overall list, be it based on updated in the last xx days, or restricted to one or two categories.
RE: Support SharePoint List Views
This would also help if we have more than 12 complex/compound columns in a sharepoint list. We could separate out columns into different views and append in Query Editor after the data is brought in. So needed - Yesterday!
RE: Support SharePoint List Views
PowerBI desktop access to SharePoint list view
RE: Support SharePoint List Views
On SharePoint, you have the option to export a list to an Excel or Access file to work with the data. Excel and Access maintain a connection to the SharePoint list, and you can refresh at anytime. You may also export a personalized view, with only the columns you want, in the order you want, and further filtered by other view parameters you set.
It would be great to have an export to .pbix file option to establish a connection to a particular SharePoint list/view. Currently the workaround is to export a SharePoint list/view to Excel or Access, then connect to that file using Power BI Desktop. This would remove that step and create an active and refreshable connection straight from the SharePoint list.