SharePoint Online and Power BI –Using SharePoint list as data source in Power BI report.

Introduction:

Hello Techies, in this blog I am going to describe how we can use SharePoint list as a data source in Power BI. Power BI have some predefined connectors for SQL, SharePoint, Dynamics CRM, File, etc. Let’s see how we can get data from SharePoint list and use it as a data source.

Prerequisite:

• Power BI desktop application
• SharePoint List

SharePoint List

I have a SharePoint list named “product price” and it has some data in it. We can get the list data into Power BI by above mentioned connectors.

Fig 1: SharePoint Online – List – Product Sale

Get Data from SharePoint List:

The above-mentioned connections can be accessed from ribbon menu of Power BI desktop application. On the home tab click on the Get data and again click on more, it will open the popup window which lists all the available connections.

Fig 2: Power BI – ribbon – Get Data

Here, under online services you can find the connector for “SharePoint Online List”.

Fig 3: Power BI – Get Data – SharePoint Online List

From online services list select “SharePoint Online List” and provide your SharePoint site/site collection site URL (Fig 4).

Fig 4: Power BI  – Get Data – SharePoint site URL

After providing site URL it will ask for authentication.

Fig 5: Power BI – Get Data – SharePoint sign in

Here, select the “Microsoft Account” tab and click sign in. It will ask for credentials if you are not signed in to power bi application (Fig 5). Once sign in done then click on connect button.

Once the connection is successfully done then another popup window will open which will show us the available list in the site/site collection (Fig 6).

Fig 6: Power BI – Get Data – Navigator

Fig 7: Power BI – Get Data – Fields

Once the loading of list data is completed, it will be listed under “fields” (Fig 7) and the imported list/table is called “data set” in Power BI.

Fig 8: Power BI – Get Data – Report

Now, using this data set we can build interactive reports and publish it to the Power BI workspace, build dashboard, etc.

In my future blogs, I’ll demonstrate how to build reports using visuals and use of DAX () in Power BI.

Thanks for reading 🙂

Keep reading, share your thoughts, experiences. Feel free to contact us to discuss more. If you have any suggestion / feedback / doubt, you are most welcome.

Stay tuned on Knowledge-Junction, will come up with more such articles