SharePoint Online and Power BI –Using SharePoint list as data source in Power BI report.
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.
- Power BI desktop application
- 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