Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

Power BI with SharePoint data: The Ultimate Guide

Nowadays, we deal with huge amount of raw data to make business decisions using Business Intelligence technology that helps in accelerating and improving decision-making, optimizing business processes, gaining new revenues and competitive advantage over business entrants. There are many BI tools are available in the market such as Tableau, Qlik, Power BI etc.

Microsoft Power BI is a cloud-based service that enables to collect, filter, combine, analyse and visualise and finally publish your data in form of an analysed report or a visualised report. Power BI provides a simple and user-friendly interface that can be used by business and power-users easily. Also, it allows collecting data from wide range of data sources. If you’re a business or power-user who wants to work robustly on data of your organisation, then you will be using Power BI Desktop or Power Bi Desktop Pro which is free to download.

In this blog, we’re going to use Microsoft Power BI tool to collect data from various sources such as SharePoint, web services, OData and SQL Azure. we’ll explore Power BI Desktop tool to build productive and creative reports. Also, we’ll use Power BI service to publish reports and set up automatic data refresh so everyone can see updated information. So, let’s get started!

Analyze SharePoint List Data using Power BI

To pull the data from SharePoint List and display in Power BI, we’re going to follow steps;

  1. Browse and signup into your SharePoint Online site and Create a SharePoint List, for example, “EmployeeList”

  2. Now, Install Power BI Desktop from here and Sign in with your office365 credentials.

  3. Now, go to “Home” Menu and click on “Get Data” to import data from SharePoint List.

  4. Click on “More…” option and search “SharePoint” to select SharePoint Online List as depicted in below screen.

  5. On the following screen, provide your SharePoint Online list URL and click on Ok button to continue.

  6. On “Navigator” screen, select your SharePoint Online List to get the data from your list and click on “Load” button.

  7. Now, select the Visualization from the imported list that you want to show on your report.

  8. Save your report and click on Publish button on the Ribbon.

  9. On “Publish to Power BI” screen, Select My Workspace option as a destination and publish the report on Cloud.

  10. Now, login into Power BI version in the Browser and Click on Datasets under My workspace section of the left navigation

  11. Check the “Data source credentials” settings for the EmployeeReport and Edit credentials for your report by selecting OAuth

  12. click on “Schedule Refresh” settings and add time to refresh your data on the report and click on Apply button

  13. Now, we’ll create Dashboard for your report and Share with other using embed code.

  14. Go to “My Workspace” and click on Create button.

  15. To add a report on Dashboard, go to Reports in My workspace in the left panel and click on “EmployeeReport”.

  16. Click on “Pin Live page” to insert report on Dashboard and select existing/new Dashboard On the “Pin to Dashboard
    screen as depicted below.

  17. To share your report on public website using embed code, click on File menu on the report’s Dashboard and select “Publish to web” option

  18. On the following screen, click on “Create embed code” button to publish

  19. Now, you can get the embed code from the popup window to share.

  20. Now, we are done with the report and it’s accessible with URL given for the sharing.

Import data from an OData feed using Power BI

The Open Data Protocol (OData) is a REST-based protocol for querying and updating data that is built on standardized technologies like HTTP, ATOM/XML, and JSON. Now, we’re going to import data from our SharePoint Online REST (OData) service https://siteurl/_vti_bin/listdata.svc

So, let’s start

  1. Open your Power BI Desktop and Sign in with your office365 credentials.

  2. Now, go to “Home” ribbon tab and select Get Data.

  3. Click on OData feed from the Menu and provide OData service URL on the “OData feed” popup screen.

  4. On the following “Navigator” screen, you need to select the table that you want to include in the report and click on Load button to continue.

  5. On the following screen, select the Visualization for your report and provide columns for the filters.

  6. Save and publish the report. Select My workspace on the “Publish to Power BI” screen and click on the select button to continue.

  7. On the following screen, you see the report publishing is done successfully,

  8. Now, open your web browser to check the published report on the cloud Power BI.

Import SharePoint Search data to Power BI

SharePoint search enables user to find information quickly and easily. It provides search API for the more advanced custom solution. Also, We can use these building blocks to get data from SharePoint search to Power BI. It allows a user to find records based on specific contents. Here, we’ll do it step by step. So let’s start

  1. Open an instance of Power BI and sign in with your Office 365 credentials.

  2. Click on “Get Data” link from the left panel of the screen.

  3. On the following screen, Click on Other type and select the Web option from the list. Click on Connect button to continue

  4. You need to provide your SharePoint Search API URL in the given text box and click on OK button for next step

    Here is basic URL for SharePoint Search API –

  5. On the following Navigator screen, you will see your search related result that you can use in Power BI report. Select the data that you want to filter and click on Load button to continue…

  6. Now, you can choose a Visualization for your report and select fields that you want to show on the report.

  7. Now, you’re done with the report and can deploy this new report on Power BI cloud.
  8. On the following screen, Go to File Menu and select Publish option. It will prompt you to save the report.

  9. On the next screen, select My Workspace on Publish to Power BI screen and click on the Select button to publish the report.

  10. Finally, you can access your deployed report in Powe BI browser under my workspace.

SharePoint data synched from SQL Azure to Power BI using SQList

As many of us are familiar that exporting data from SharePoint to SQL server never been easy. It requires custom development or BCS integration to fatch data via External Content Types. Here, I have used a great tool that simplifies hurdle of this integration of SharePoint to SQL Database. SQList (a Axioworks product) is a tool that allows you to export SharePoint data to SQL server in the fast and easy way. So, let’s see how it works

  1. You need to download a trail/license version of this tool from here and follow the instruction to install it.
  2. Once, you have installed this tool. You can open an instance of SQList Manager which allows you to copy your SharePoint data to target SQL Server database. click New Replication button to continue…

  3. On the following screen, there are six stages given to export data from SharePoint to SQL Server. We’ll go on each stage one by one.

  4. On the below screen, you will provide your SharePoint site details and select an Authentication mode to establish connection as depicted below in this screenshot.

    Note – This application does not support Multi-factor authentication. And, if you have enabled Multi-factor authentication for your Office 365 login. You need to create an app password to establish connection with SharePoint site. Click here for more details.

  5. Once, you have entered all required details. You can test the connection by clicking “Test Connection” button. If the connection is established. It should open a “Connect successful” popup window. Click on “OK” button to continue…

  6. Click on “Next” button to move on next stage where you will be providing for your destination database details. Using this application, you can export your data to any version of SQL Server starting SQL Server 2005. Also, It is integrated with Microsoft Azure SQL database service. So, you can export your data on Azure seamlessly.

  7. On the following screen, you need to enter your SQL Server details to establish a connection to export data. Click on Test Connection button to test connection parameters for your Database engine. Click on Next button to continue.

  8. Next stage is to select an export type, you can choose to export data from your top level site. Also, you can export data from site’s subsite by setting the deep levels. Choose Export lists and libraries from this site the only option and click on Next button to continue..

  9. On the following screen, there are two options in the By Default dropdown to export the data for selected lists and libraries. You can select lists and libraries with content that you want to export like attachment, binary content and deleted items etc. Click on Next button to continue…

  10. Here, we have completed with export wizard. Click on Finish button and close the window.

  11. Now, we’ll open an instance of SQL Server and connect with Microsoft SQL Azure to verify the exported data. Enter your SQL Azure server name and credentials and click on Connect button to continue…

  12. On the following screen, Open Object Explorer in the SQL Window and click on Databases. you can see the exported SharePoint lists and libraries inside the Tables folder.

  13. Now, we can check data inside exported tables by executing “Select” query as depicted below in the screenshot. We have checked our exported data in the SQL Azure. Now, we get fetch data from SQL Azure to Power BI.

  14. Open an instance of Power BI Desktop, and click on Get Data from the left panel of the screen.

  15. On the following screen, Click on Azure and select Azure SQL database to connect with exported data. Click on Connect button to continue..

  16. On the popped up window, you need to enter your Azure SQL Server Name and database from which you want to import data into Power BI.

  17. On the next window, you need to authenticate your connection by providing your Azure SQL database credentials. Click on Connect button to continue…

  18. Now, you should be able to see your imported tables on Navigator screen. Verifiy your tables and select the tables that you want to use for reports.

  19. Power BI will load the data by evaluating it. Just wait for a couple of seconds to see data in the Power BI.

  20. On the following screen, you need to select the report format for the Visualization and columns that you want to place on the report. You should be able to see the reports on the screen.

Well, Done!

Now, You should be able to import you SharePoint data to Power BI. This blog helps you to understand Power BI Desktop and Power Bi web Browser versions and features of both. Also, it helps you to bring data from your various data sources to Power BI and design your visualizations for the reports.

Hope, this will be helpful for you!

Keep reading and learning

Thank you!!1b


Latest articles

Our Favourite Picks from Power BI Global Summit 2023

17 Mar 2023

Power BI Summit is the biggest Power BI conference of the year which ran virtually from 6th March to 10th March. It brought speakers from the […]
[read article]

AxioWorks Newsletter February 2023: Development of SQList new release is in full swing

28 Feb 2023

Hello Reader, Welcome to another exciting issue of our monthly Newsletter! Each month, we try our best to bring you content that is relevant and […]
[read article]

MS List Templates bundled with Power Automate Flow

20 Feb 2023

Microsoft Lists previously known as SharePoint Lists is the go-to option for maintaining and storing data in the world of O365 and its usage has […]
[read article]

AxioWorks Newsletter January 2023: Kicking off 2023 with some great content

30 Jan 2023

Hello Reader, Welcome to another exciting issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[read article]

A pragmatic approach to obtaining value from your SharePoint data

20 Jan 2023

Organisations have been storing data in SharePoint for more than 20 years.  This data can be as simple as a List used by a small team or a complex […]
[read article]

Power BI Report Server : An On-Premises Report hosting Suite

16 Jan 2023

Power BI has become the most widely used tool for reporting due its user-friendly interface which allows business users to generate sleek and […]
[read article]

AxioWorks Newsletter December 2022: Good bye 2022, welcome 2023!

29 Dec 2022

Hello Reader, Welcome to the December issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[read article]

PowerApps Debugging and Troubleshooting made easy with Monitor

28 Dec 2022

Troubleshooting and debugging are an integral part of building and supporting a resilient app especially when it is built using a low-code […]
[read article]

AxioWorks Newsletter November 2022: Is it almost Xmas already?

29 Nov 2022

Hello Reader, Welcome to the November issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[read article]

Future of SharePoint in the world of Power Platform

29 Nov 2022

Microsoft SharePoint has been the driving force for enabling virtual team collaboration and the modern workspace revolution over the last three […]
[read article]

Get SharePoint and SQL news to your inbox

Stay up-to-date with industry news and trends, SQL and SharePoint innovations and all the latest from AxioWorks by subscribing to our monthly newsletter.

Subscribe to the AxioWorks newsletter