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 –
    http://siteurl/_api/search/query?query_parameter=value&query_parameter=value


  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

AxioWorks Newsletter September 2023: An exhilarating month.

30 Sep 2023

Hello Reader, September has been a bustling and exhilarating month for us at AxioWorks, filled with advancements, collaborations, and a whole lot of […]
[read article]

The Collaborative Pathway: Bridging Minds in the AI Ecosystem

19 Sep 2023

In recent days, I found myself immersed in an enlightening conversation with a friend, a fellow entrepreneur steering his ship in the vast sea of […]
[read article]

AxioWorks Newsletter August 2023: A month of milestones and innovation.

28 Aug 2023

Hello Reader, September seems to be a month of unexpected milestones and continued innovations at AxioWorks. As the summer wanes and the autumn […]
[read article]

Use Graph API in Power Automate to read large Excel files from SharePoint

28 Aug 2023

Power Automate, a part of Microsoft’s suite of tools, is increasingly becoming a preferred choice for automating workflows and business […]
[read article]

From Punch Cards to Python: Still Hitting the Keys in my 50s

16 Aug 2023

I spent the entire weekend writing code, not because of deadlines to meet or million-pound dreams, but for the kick that I still get from doing it. […]
[read article]

AxioWorks Newsletter July 2023: Unveiling AxioWorks’s latest successes and exciting innovations

27 Jul 2023

Hello Reader, We hope this monthly update finds you in good spirits. As always, we are eager to share the latest developments and exciting ventures […]
[read article]

AI and Data: The Symbiosis of Interaction and Accuracy

13 Jul 2023

Artificial Intelligence (AI) has rapidly emerged as a revolutionary technology that fundamentally alters the nature of human-machine interactions. […]
[read article]

AxioWorks Newsletter June 2023: SQList v8 Release, Webinars, and New Product Development

30 Jun 2023

Hello Reader, Greetings to all our valued customers and subscribers! We are thrilled to share some exciting updates and developments with you in this […]
[read article]

Safeguarding Secrets with Secure Strings and Environment Variables in the Power Platform

26 Jun 2023

In today’s interconnected digital landscape, safeguarding sensitive information and maintaining robust security measures is of paramount […]
[read article]

Case study: Streamlining Medical Trial Data Analysis in the Pharmaceutical industry

15 Jun 2023

In the ever-evolving landscape of the pharmaceutical industry, conducting medical trials and analysing the resulting data are critical for the […]
[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

*