Excel to Insights: Integrating SharePoint & Power BI for Data Mastery

Introduction

Discover the seamless integration of SharePoint Online with Power BI to transform your data into compelling visuals. This guide takes you through each step, from setting up your SharePoint lists to importing them into Power BI for advanced analysis.

YouTube Video

YouTube Video Link

There is a YouTube Video we made that goes over the whole process if you like to watch content rather then read it. You can come back here for more detail in the process.

Preparing Your SharePoint Online Environment

Creating structured, organized data is the backbone of any insightful analysis. SharePoint Online offers a robust platform for such organization with its versatile lists feature. Below, we delve into the nuances of SharePoint lists and the initial steps required to transform your raw data into a visual storytelling tool with Power BI.

Creating a Microsoft SharePoint List

A SharePoint list is more than just a data storage space—it’s the starting point for collaborative data management. To set the stage for your data journey:

Initiate a New List

Begin by navigating to your SharePoint site and creating a new list. You can start from scratch or use one of the many templates available to jump-start your process.

Add the Lists App to Teams
Add Lists App In Teams

Start By clicking on the + Sign to add the Microsoft Lists App to your Microsoft Teams Team.

Teams App Menu

Search for “Lists” or select it if it’s the first option.

Confirmation of adding the Lists App.
Complete

This is the confirmation that the Lists App has been added successfully to the Teams Channel.

New List or Existing List

Select Create a List or Add an Existing List if you have one already. We’ll go through building a Microsoft List from scratch.

Select a Blank List or a Template in Microsoft Lists.
List Options

You can import From Excel if you’d like, we’re going through the manual process and selecting Blank List.

Create an Empty List
Specify List Details

Give the List a Name, Description, select a color, and icon. Then click on Create.

Empty List
Empty List Ready for Data

We have an empty list ready for data.

Data Entry and Structure

Populate your list with data, ensuring each column is defined with the correct data type. This is crucial for accurate data representation in Power BI later on.

Personally, I find it easier to do bulk edits in the web browser instead inside Teams. You have more screen real estate that way.

Open In SharePoint Link
Open In SharePoint Link

Click on the 3 dots and select open in SharePoint.

Renaming the First Column
Renaming the First Column

Go ahead and rename the first column to the column name in your excel file.

Creating Columns
Creating Columns

Then we can continue on creating the rest of the columns.

Be mindful of the data type. You can set the data type in SharePoint and Power Bi will recognize it and apply those changes automatically.

1 Row of Data Test
1 Row of Data Test

Copy the Data from the first row in Excel. Then, select the entire row in SharePoint and Paste the data in. Verify the data comes over as expected with column types.

Mass Import

Now Select and copy the rest of your data from the Spreadsheet, Highlight the next row in SharePoint and Paste it all in. Watch how each row get pasted in and saved to SharePoint.

Customization for Clarity

Customize your list views to highlight the most important data points, creating an intuitive interface for users who will interact with this data.

Data Importing Techniques

Importing data into SharePoint lists should be done with care to maintain data accuracy and integrity:

Import Options

Utilize SharePoint’s built-in import capabilities, which allow you to bring in data from Excel or other databases smoothly.

Maintaining Data Quality

Double-check your data types and column settings during the import to prevent any misalignment that could affect your analysis in Power BI.

Importing SharePoint Lists into Power BI

The true magic of data comes alive when you transition from data storage to data visualization. Power BI’s prowess lies in its ability to take SharePoint lists and turn them into interactive, actionable insights. Let’s dive into how you can bridge SharePoint Online with Power BI to illuminate your data’s story.

Connecting SharePoint to Power BI

Connectivity between SharePoint and Power BI is a gateway to advanced analytics:

Data Linking

Begin by selecting “Get Data” in Power BI and choose SharePoint Online list as your source. This will establish a direct pipeline from your SharePoint data to Power BI.

Power Bi Get Data Page
Get Data

Open Power Bi and Click on Get Data.

Search for SharePoint to filter data sources.
Search for SharePoint

You can scroll through the list of options to find SharePoint Online List. Or the better option is to search for SharePoint to shrink the list down to 3 options. Select SharePoint Online List.

Enter SharePoint Site URL
SharePoint Site URL

Paste in the URL to the SharePoint Site. This is just the site URL, NOT the direct link to the SharePoint Online List.

Find the SharePoint List
Find The List

Find the SharePoint Online List in the Site List. There are a bunch of hidden built in lists that SharePoint uses behind the scenes. Click on Transform Data to clean up the list.

Review and Transform the data.
Review and Transform the Data

Update Column names if needed. If you set your Columns correctly then you shouldn’t have to change the column types in Power Bi.

Delete the extra SharePoint System Columns
Delete the Extra Columns

Scroll to the right and delete the extra system columns. You’ll never use these columns.

End Result

Click Close and Apply, then view the results in the Table View in Power Bi.

Scheduled Refresh

Set up a scheduled refresh to keep your data current. This ensures that your reports and dashboards reflect the most recent data, providing a live pulse on your operations.

Visualizing SharePoint Data in Power BI

With your data now in Power BI, it’s time to unleash your creativity:

  • Dashboard Design: Craft dashboards that not only tell a data story but also resonate with your audience. Utilize Power BI’s rich palette of visualizations to highlight key metrics and trends.
  • Custom Visuals and Branding: Personalize your reports with custom visuals and branding elements to align with your organization’s identity, making your data outputs both informative and visually appealing.

Leveraging Excel with Power BI and SharePoint

The synergy between Excel, Power BI, and SharePoint is a trifecta for robust data analysis. This final section of our guide will provide you with the expertise to use Excel as a powerful ally in your Power BI and SharePoint strategy.

Using Excel Files from SharePoint Document Libraries

Integration Essentials

Learn how to connect Excel files stored in SharePoint Document Libraries directly to Power BI. This connection not only streamlines the data analysis process but also keeps it centralized.

Microsoft Teams Files Tab to hold excel files for Power Bi
Teams Files Tab

Upload the excel file(s) to the intended SharePoint Document Library or Microsoft Teams Files Tab

ProTip:

Edit the excel file right before starting this process so you can sort the list by date modified to bring it to the top of the results page.


Power Bi Desktop Get Data Window
Power Bi Desktop Get Data

Open Power Bi Desktop and Select Get Data to get started.

Power Bi SharePoint Options Including SharePoint Folder, SharePoint List and SharePoint Online List.
Power Bi SharePoint Options

Scroll or Search for SharePoint and Select SharePoint Folder

SharePoint URL inside Power Bi
SharePoint URL inside Power Bi

Paste in the URL to the Base SharePoint Site, not the direct link to the SharePoint document library.

Document Library Results

Find the Excel/CSV file in the list of documents. Power Bi does not honor the folder view the SharePoint documents have. It’s displayed as one giant list.

If you followed my Protip you can sort by Date modified to bring your file(s) to the top of the list.

New Query Option in Power Bi data transformation.
New Query Option in Power Bi

Right Click on the file and select Add as New Query.

Excel File Loaded into Power Bi

Double Click on the file to open it.

Initial Opening of the Excel File in Power Bi
Initial Opening of the Excel File

The file is opened in Power Bi Data Source Mode and may need a little clean up.

Use First Row As Headers to clean up the columns.
First Row As Headers

Click on use first row as headers if needed.

Delete Original Query of the Entire Document Library.
Delete Original Query of the Entire Document Library

Repeat as needed for additional files or Tables from the document library.

Delete the original connection to the document library. You do not need Power Bi inventorying the files in SharePoint.

Final Excel File In Power Bi for this integration.
SharePoint Excel file in Power Bi

Finish any last data conversions or transformations in Power Bi then Click on Save and Apply to bring your data into Power Bi.

Real-Time Analysis

Understand the process of setting up real-time data analysis, ensuring that your Power BI reports always reflect the latest updates made to the Excel files in SharePoint.

Best Practices for Data Management

  • Data Hygiene: Maintain data integrity by regularly cleaning and structuring your Excel files before they feed into Power BI. This practice is essential for accurate reporting.
  • Excel’s Advanced Features: Explore how Excel’s advanced functionalities, such as Power Query and pivot tables, can augment your Power BI reports, providing a deeper level of analysis and insight.

Conclusion

Embracing the integration of SharePoint, Excel, and Power BI elevates your data from simple spreadsheets to strategic insights. This guide not only walked you through each technical step but also aimed to inspire confidence in harnessing these tools for more informed decision-making. Whether you’re tracking sales, analyzing market trends, or managing projects, the seamless blend of these Microsoft applications offers a competitive edge in data management. Start leveraging this powerful trio and transform your data into a visual narrative that drives business success.

Additional Resources for Power Bi and SharePoint Lists