Blogs

Power BI > Sharepoint Online List as Data Source

Inkey, July 19, 2019 3763 Views

Power BI has been growing day by day with the new features that the Power BI team is bringing every month.

One of the great features for those who are working with Sharepoint Online is that we can use “Sharepoint Online” List as a data source in Power BI to get an overview about Sharepoint sites and Sub-sites.

Please note that Sharepoint subscription is required for this approach.

Let us illustrate how can we get the data from the Sharepoint site. Here, we will fetch information for the documents that are uploaded in our Sharepoint site. For that, let us upload a few documents in Sharepoint site as follows:

For illustration purpose, we have uploaded two documents in our Sharepoint Site

Let’s start with building some supporting tables.
For the Documents, we’ll have Documents Table – “InkeySolutions Team Documents”.

And then, we will have Dates table(Calendar), which can be created by the following DAX formula:

CALENDAR(DATE(2019,01,01),DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())))

You can use this Calendar table to perform date and time related operations.

For “InkeySolutions Team Document” table we’ll Get Data from Sharepoint Online List as shown below :

Here we need to provide the Site URL for our Sharepoint Website as Data source :

After providing the Site URL it will ask for Authentication for your site if you have not previously logged in:

After it gets signed in, the Navigator dialog box appears as shown below:

You can see the various options that we can extract from the Sharepoint Site. I will go for Documents as I want the information of documents uploaded on the site.

Here you can directly load the Documents list or you can edit the data of Documents table as per your need. I will go for Edit option here.

As we have uploaded two files on the site previously you can see the two records are fetched from the documents of our site:

You can get the information about files by extracting File column from the fetched column. You can choose the other columns along with File column as per your need. Here I will go for File column only:

You can extract the required information such as Length, Name, TimeCreated, TimeLastModified, Author, etc. from File column as per your need:

As shown below two files which we have uploaded earlier are loaded successfully:

Once loaded, we can create appealing reports using the information which we have extracted. Here I have created a sample report using these pieces of information for three sub-sites created:

You can thus create useful and appealing reports as per your need using Sharepoint Online List.

Happy Reporting!