SharePoint API use in Power BI
If this is your first time working with APIs in Power BI, I recommend you check the article “Power Query Web.Contents cheat sheet walkthrough”.
When working with Power BI and SharePoint, if you run into long load or time-out issues, it’s probably time to start using SharePoint’s API, as it is a better alternative. Using SharePoint’s API can dramatically reduce load times from several minutes to seconds, making it less prone to time-outs.
SharePoint API’s better performance comes from two factors: the ability to batch responses and request only the required columns. Yes, it is a bit more complex, and you will have to deal directly with M, but remember #MisForMagic, and it is always fun.
Why Use SharePoint API?
There are a few key advantages to using the SharePoint API to connect to SharePoint data in Power BI instead of using the SharePoint list connector:
Advantages
-
More control over the data: Using the SharePoint API allows users more control over the data they access. This is because the API enables users to specify the SharePoint list or library they want to access and the columns they want to use. In contrast, the SharePoint list connector automatically pulls in all the data from a SharePoint list or library.
-
Improved performance: The SharePoint API allows users to select only the data they want to use. It can be faster and more efficient than the SharePoint list connector. This is because the connector will pull in all the data from the SharePoint list or library, which can take longer and use more resources.
-
More flexibility: The SharePoint API allows users to access data from multiple SharePoint lists and libraries within a single Power BI semantic model. This can be useful for combining data from different sources and creating more complex reports and dashboards. In contrast, the SharePoint list connector will only allow users to access data from one SharePoint list or library at a time.
Disadvantages
-
Higher technical knowledge required: The SharePoint API requires more technical expertise than the SharePoint list connector. This is because the API requires users to specify the exact endpoint they want to access, which can be complex and requires a good understanding of how the API works. In contrast, the SharePoint list connector is more user-friendly and requires less technical knowledge.
-
More setup required: Because the SharePoint API requires users to specify the endpoint and columns they want to access, it requires more configuration than the SharePoint list connector. This can be time-consuming and may not be worth it for users who only need to access a small amount of data.
How to Use SharePoint API
1. Basic Setup and Authentication
To connect Power BI to SharePoint through the API, users must ensure they have the necessary permissions to access the SharePoint data.
Once you’ve confirmed having the necessary permissions, you can follow these steps:
-
Open Power BI and select the “Get Data” option from the toolbar.
-
In the “Get Data” window, select the “Web” option from the “Other” category.
Get data from web
- Enter the URL for the SharePoint API endpoint in the “Web Address” field and click “OK.” This URL should be in the format of:
https://<sharepoint site>/_api/web/lists/getbytitle('<list name>')/items
Enter URL to get data from
This is the equivalent M code (#MisForMagic):
- Power BI will prompt you to enter credentials and choose Organizational account. Sign in with the credentials for the user account that has access to the data and click “Connect.”
Add authentication credentials
- The SharePoint data will then be loaded into Power BI, but you might notice that the data was loaded in XML format. This might be all right for some people, but I am used to dealing with JSON; this is why we will modify the headers.
2. Advanced Query Parameters
That was the basic SharePoint API request; now, let’s modify this request.
We will include additional query parameters to our requests in the following query:
- The first query,
$top, specifies the batch size. - The second query,
$expand, is where we specify the columns that need to be expanded; the reason is that in SharePoint, some columns are returned as records, and we need to instruct the API that we will require some information from the record. - In the last query,
$select, we specify the columns we want in the response; if the column comes from an expanded column, then we need to include the “parent” record as a prefix.
3. Understanding Internal Column Names
The display name of a column is the name that is shown to users when they view the list or library. The internal name of a column is the name that is used internally by SharePoint to identify the column. It is the name that is used in the list’s underlying database and in the list’s schema.
It is important to understand the difference between the display name and the internal name because the internal name is used when you are working with the list or library programmatically, such as when you are working with SharePoint’s API.
This code will come in handy to get the internal name of SharePoint columns:
4. Retrieving Binary Files
It is possible to retrieve binary content from a SharePoint Document library via the API. This is done by first retrieving the ServerRelativeUrl property and then using the endpoint “GetFileByServerRelativeUrl”.
I have not yet tried the performance of this method versus the SharePoint folder connector, but it may come in handy for someone.
5. Paging SharePoint Results
This query was a challenge. Although I successfully created a query that could page through SharePoint APIs using the List.Generate function, I was not able to make it refresh in the service because of a dynamic source error. Fortunately, after digging for many hours I was able to find one LinkedIn post and one community question that showed me the way of doing this:
Conclusion
In conclusion, using the SharePoint API to connect to SharePoint data in Power BI offers several benefits, including more control over the data, improved performance, and flexibility. However, it also requires more technical knowledge and setup compared to using the SharePoint list connector. Carefully consider the trade-offs and whether the benefits of using the API outweigh the costs before deciding which option is right for you.
Remember, when dealing with M language and Power Query, practice makes perfect. The SharePoint API approach might seem complex initially, but the performance benefits and control it provides make it worthwhile for larger implementations or when you encounter timeout issues with the standard connector.