Graphed LogoGraphed
Login

How to Link SharePoint Excel to Power BI

Cody Schneider•

Connecting an Excel file from SharePoint to Power BI is a fantastic way to create dynamic, auto-updating reports for your team. This setup moves your reporting process from manual, repetitive data pulls to an automated, collaborative workflow. This article guides you step-by-step through the entire process, covering how to get the correct file path, make the connection in Power BI, and set up a scheduled refresh so your dashboards are always current.

Why Connect SharePoint Excel to Power BI Instead of Your Desktop?

While you can connect Power BI to an Excel file on your computer, hosting that file in a SharePoint document library offers several key advantages for business reporting:

  • Automation is simple: Once you publish your report to the Power BI Service, you can schedule automatic refreshes. Power BI can access the cloud-based SharePoint file anytime to get the latest data, without needing access to your local machine.

  • Collaboration is built-in: Multiple team members can update the Excel file in SharePoint simultaneously. The next time the Power BI report refreshes, it will pull in everyone’s changes seamlessly, creating a single source of truth.

  • Version control is eliminated: Say goodbye to file names like "Sales_Report_v4_final_FINAL.xlsx". Everyone always works from the most current version of the file in SharePoint, reducing confusion and errors.

  • Accessibility from anywhere: Your data source lives in the cloud, so you and the Power BI Service can access it from anywhere with an internet connection. You’re not tied to your C: drive or a shared network folder.

Before You Connect: Prepping Your Excel File

A great Power BI report starts with well-structured data in Excel. Before you even open Power BI, take a few minutes to prepare your Excel file. This will make the connection process far smoother and prevent frustrating errors later on.

Format Your Data as a Table

This is the single most important step. Power BI works best with structured tables, not loose ranges of cells.

  1. Click anywhere inside your data in your Excel file.

  2. Press Ctrl + T (or go to the Home tab and click "Format as Table").

  3. In the "Create Table" popup, ensure the checkbox for "My table has headers" is ticked. Click OK.

  4. Your data is now in a structured table. Excel will automatically apply some formatting, which you can change if you like.

Why is this so important? An Excel table is a dynamic object. When you add new rows of data to the bottom, the table automatically expands. When you connect Power BI to this table, it will always see all the data, even the new rows you add later.

Give Your Table a Descriptive Name

By default, Excel names your tables "Table1," "Table2," and so on. When connecting to Power BI, you'll have an easier time if you give it a clear, descriptive name.

  1. Click anywhere inside your table.

  2. A new "Table Design" tab will appear in the ribbon. Click on it.

  3. On the far left, you will see a box with the "Table Name." Replace "Table1" with something meaningful, like "MonthlySalesData" or "CampaignPerformance". Use a single word with no spaces for best results.

Step-by-Step Guide: Linking Your SharePoint Excel File to Power BI

With your Excel file properly formatted and saved to SharePoint, you’re ready to make the connection in Power BI Desktop. The key to making this work for scheduled refresh lies in using the correct method to get the file path.

Step 1: Find the Correct File Path in SharePoint

This is the most common place where people run into trouble. Simply copying the URL from your browser's address bar will not work for automatic refreshing in the Power BI service. You need to get a cleaner path by opening the file in the Excel desktop app directly from SharePoint.

  1. Navigate to the document library in SharePoint where your Excel file is stored.

  2. Find your file and click the three dots (…) next to the filename.

  3. From the menu, select Open, and then choose Open in app. This will launch the Excel desktop application and open the file directly from the cloud.

  4. Once the file is open in the Excel desktop app, go to the File tab in the ribbon.

  5. Click on Info.

  6. You will see the file path at the top. Click the Copy path button to copy the full SharePoint location to your clipboard.

Step 2: Clean Up the File Path

The path you just copied is almost perfect, but it includes a small instruction at the end that needs to be removed. Paste the path into a text editor (like Notepad) to easily edit it.

Your path will look something like this:

https://yourcompany.sharepoint.com/sites/Marketing/Shared%20Documents/Campaigns/Marketing_Data.xlsx**?web=1**

The part you need to remove is the query string at the very end: ?web=1.

Simply delete that part of the URL. Your final, clean path should look like this:

https://yourcompany.sharepoint.com/sites/Marketing/Shared%20Documents/Campaigns/Marketing_Data.xlsx

Copy this modified URL. This is the link you will use in Power BI.

Step 3: Connect Power BI to the SharePoint File

Now, it's time to switch over to Power BI Desktop.

  1. Open a new or existing Power BI report.

  2. In the Home tab of the ribbon, click Get Data.

  3. From the dropdown menu, select Web. It's crucial that you use the 'Web' connector, not the 'SharePoint Folder' or 'SharePoint Online List' connectors for this method.

  4. A dialog box titled "From Web" will appear. Paste your cleaned-up file path from Step 2 into the URL box.

  5. Click OK.

Step 4: Authenticate and Select Your Data Table

Power BI will now try to connect to the SharePoint file and will need to verify you have permission to access it.

  1. An "Access Web content" window will appear. Select Organizational account from the options on the left.

  2. Click the Sign in button and use your Microsoft 365 / work account credentials to log in.

  3. Once you've successfully signed in, click Connect.

  4. Power BI will connect to the file and a Navigator window will pop up, showing you all the available data elements from your Excel workbook.

  5. You will see both the worksheets (with a little worksheet icon) and the table you created (with a blue-header table icon). Always select your named table – in our example, "MonthlySalesData".

  6. A preview of your data will appear on the right. You can either click Load to bring the data directly into your report canvas or Transform Data to open the Power Query Editor for further cleaning and shaping. For simple data, 'Load' is fine.

That's it! Your SharePoint Excel file is now linked as a data source in Power BI.

Keeping it Fresh: Setting Up a Scheduled Refresh in Power BI Service

The real magic happens when you automate this connection. To do that, you need to publish your report to the Power BI Service and configure a refresh schedule.

  1. After building your report in Power BI Desktop, save it, then click the Publish button on the Home ribbon. Choose a workspace to publish it to.

  2. Open a web browser and navigate to app.powerbi.com. Go to the workspace where you published your report.

  3. Find the newly published dataset (not the report). It will have the same name as your report file.

  4. Hover over the dataset and click the three dots (…) and select Settings.

Configuring Data Source Credentials

You need to tell the Power BI Service how to log in to SharePoint to get the Excel data.

  1. In the dataset settings, expand the Data source credentials section.

  2. You will see an error noting that the credentials are not valid. Click Edit credentials.

  3. In the pop-up window, set the Authentication method to OAuth2 and the Privacy level to Organizational.

  4. Click Sign in and log in with your same Microsoft 365 work credentials. Once complete, the connection will be authorized.

Setting Your Refresh Schedule

Now you can tell Power BI exactly when to update the data automatically.

  1. In the same settings screen, expand the Scheduled refresh section.

  2. Toggle the switch to turn scheduled refresh on.

  3. Beneath this, you can set the Refresh frequency (e.g., Daily) and choose your time zone.

  4. Click "Add another time" to specify one or more times of day for the refresh to run.

  5. Optionally, you can enter an email address to receive refresh failure notifications. This is a good way to monitor if the connection breaks for any reason.

  6. Click Apply at the bottom.

Your report will now automatically fetch the latest data from the Excel file in SharePoint according to the schedule you set. Any changes made by your team will be reflected in the Power BI dashboard without any manual effort.

Final Thoughts

Connecting a SharePoint-hosted Excel file to Power BI is a stellar way to build a reliable, automated reporting workflow. This technique lets your team collaborate effectively on the source data while ensuring your executive dashboards always reflect the latest changes without requiring you to manually import and refresh anything.

Manually configuring these connections and fine-tuning refresh schedules in Power BI is an important skill, but it has a considerable learning curve with several tricky steps. At Graphed , we streamline this entire workflow. You can connect your marketing and sales data sources, including Google Sheets, and simply describe the charts and reports you need in plain English. Our AI takes care of building the live dashboards and handling the data refreshes for you, turning tedious report building into a quick and simple conversation.