How to Use SharePoint Data in Power BI

SharePoint is a great tool for storing and working with data, but it's not always the best for presenting it to others. Oftentimes, charts are better for helping others quickly understand what data you're trying to convey. Unfortunately, SharePoint doesn't offer you the native ability to build charts. Lucky for you, SharePoint offers you the ability to transfer data into Power BI so you can build reports. Let's take a look at what you need to to use SharePoint data in Power BI.

Why You Should Use Power BI to Present SharePoint Data

It's commonly accepted in the corporate world that charts graphs and charts are the most effective ways to convey statistical information. While a data table and a pie chart can tell people the same information, the pie chart won't have people's eyes glaze over when they read it. That's why using you should be using Power BI to present SharePoint data. Power BI lets you easily generate charts and graphs that easily translate your data into something able to be understood. That said, let's get into what you need to do to get started using SharePoint data in Power BI.

How to Connect SharePoint and Power BI

1. Make sure you have Power BI desktop. If you don't have it, download it for free.
2. Open Power BI desktop. In the home ribbon select Get Data and then select More.
3. Click on Online Services and then click on SharePoint Online List.
4. Click Connect.
5. Search for the address of your SharePoint site that contains the list/data you want to use. You can find this in your SharePoint site by selecting Home in the navigation pane and then copying the address from your browser's address bar.
6. Go back into Power BI Desktop and paste the address into the Site URL field in the open dialog box.
7. Depending on your settings, you may see a SharePoint access screen come up. If not, skip to step 10. If you do see it, select Microsoft Account on the left side of the access screen.
8. Click on Sign In and then enter your Microsoft 365 username and password.
9. Once you have signed in, click on Connect.
10. On the left side of the navigator, click on the checkbox beside the SharePoint list that you want to connect to.
11. Click on Load. Power BI will now load your list data into a new report.

Once you've connected SharePoint to Power BI, your next step is to run a report using the data you connected. Here's what you need to do.

Creating a Report in Power BI From SharePoint Data

1. On the left side, click on the Data icon to check that your SharePoint list has been loaded.
2. Make it so that your list columns with numbs show the Sum or Sigma icon in the Fields pane on the right. If there are any that don't, select the column header in the table view, select the Modeling tab and then change the Data type to Decimal Number or Whole Number depending on the data. If a prompt comes up to confirm your change, click Yes. If your number is a special format, you can change that by setting the Format.
3. On the left side of the screen, click the Report icon.
4. Select the columns you want to visualize by clicking on the checkbox beside them in the Fields pane on the right.
5. Change the visual type to what you want.
6. If you want to create multiple visualizations, you can do this by deselecting the existing visual and then selecting checkboxes for other columns in the Fields pane.
7. Once you have what you want, click on Save to save your report.

After following these steps, you'll have everything set up to ensure that you're able to present visually-appealing, informative graphs featuring SharePoint data.

Conclusion

Now that you have everything set up, you should now be able to take any data you have in your site SharePoint site and use it in Power BI. This is just a sample of the things that you can do with both SharePoint and Power BI. If you are interested in learning more about using these tools, make sure to subscribe to our blog for the latest SharePoint and Office 365 tips.