Power BI connection with SharePoint Lists
Some time ago we were working on a project which is intended to generate different reports generating a connection to certain lists that were at the level of a subsite of SharePoint 2013 on premises, and also others in SharePoint Online.
Get Data – Native Access
Generally this task is presented as a native option, and relatively simple in Power BI. The process to make the connection would be: open Power BI Desktop and the option Get Data select SharePoint List
In the next step of the process simply we define the site / subsite to which we want to connect. A common mistake in this regard is to add in the URL list , and actually just typing the location of the site as Power BI take all elements of the site so we can select which one we want to connect.
On security issues is a good advice to keep in mind the way we connect, which can be using Windows credentials, another option is to add credentials for the account you will use to connect to the server, and the last option is to perform a login to the site (used in SharePoint Online):
By connecting to the site, we list all the items that we import, which will mark the lists you want to import. I recommend not go straight to the Load option just click on Edit to have more control to adjust queries:
In the case where we manage lists fields that may have links or searches against other lists, we will present the option RECORDS when connecting with that information. We can visualize each column in the upper right choice in that column to disaggregate, and open fields that is made that column. One option that is always very interesting is FieldValuesAsText that provides great value and showing the result of all search and lookup fields:
We can make various changes, eliminating unnecessary columns, transforming first lines in titles, remove blank lines, among other interesting actions before applying the sequence of steps for importing data.
Keep in mind that Power BI is recording all the steps of transformation, and a very useful action is simply to cancel one of the steps executed with a click on the right bar of this last screen. In this way we can perform various actions on consultation and could undo any step at this time.
Applied or Apply/Close after the consultation, and we can view the screen for Power BI Desktop where we will be able to start working on our report:
Tips! Another way to connect to a SharePoint list with Power BI!
This advice came because I have errors when I want to connect to certain specific subsites in SharePoint lists with native option. By selecting the first step GET DATA option, choose to OData.
After selecting this option, we establish the connection:
At this point, where we set the URL to remember that the format should be as follows:
Remember to change: SITE_URL with the real URL of your site/subsite.
On the next screen we define the authentication method you want to apply:
In this case I used the Organizational Account credentials because I am using an account for SharePoint Online site.
When selecting this option, we will list Power BI site lists all (or subsite) selected:
As in the previous case, we are with the same possibility that in a native connection to SharePoint Lists, and will have the same options regarding query, data processing and cleaning to consider for our reports.
Thus, not only we have the Get Data and select SharePoint List option, but also by using the OData also to connect to SharePoint lists with Sites and Sub sites.