Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Replace Excel File in Power BI

Cody Schneider••8 min read

Sooner or later, every Power BI user needs to swap an Excel file that acts as a data source for their report. Maybe your team reorganized shared folders, you have a new updated monthly file, or you’re finally moving that local workbook to SharePoint. Whatever the reason, changing the data source can feel daunting, with the fear that one wrong click will break your entire dashboard. This guide will walk you through exactly how to replace an Excel file in Power BI, step-by-step, without losing your work.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Why Would You Need to Replace an Excel File in Power BI?

Before jumping into the "how," let's quickly cover the "why." You'll typically find yourself needing to swap an Excel data source in a few common scenarios:

  • File Location Changed: The most common reason. The file was moved from your desktop to a network drive, or a shared folder was renamed.
  • Updated Version of a File: You might work with monthly or quarterly reports where "Report-Jan.xlsx" needs to be replaced with "Report-Feb.xlsx."
  • Upgrading to the Cloud: A smart move for any team is transitioning from a locally stored Excel file to one hosted on SharePoint or OneDrive. This enables collaboration and allows for automated data refreshes in the Power BI service.
  • Consolidating Files: You might have a new, cleaned-up master version of a file that needs to replace an older, messier one.

First, Prepare Your New Excel File

Success here is all about preparation. To ensure a smooth transition and prevent a cascade of errors in Power BI, your new Excel file must have the same structure as the old one. This is the most critical step. One small difference can cause your queries or visuals to fail.

Before you even open Power BI, check these things in your new Excel file:

  • Column Headers: Make sure the column names are identical to the original file. "Sales" is not the same as "Sales " (with a trailing space).* Check for spelling, casing, and spacing.
  • Sheet or Table Name: If your Power BI query connected to a specific worksheet (e.g., "Sheet1") or a named table (e.g., "Sales_Data"), the new file must contain a sheet or table with that exact same name.
  • Data Types: Ensure columns have the same type of data. If a "Date" column in the original file suddenly has text in the new file, Power BI will throw an error during refresh.

Once you’ve confirmed the new file is a structural match for the old one, you're ready to make the switch in Power BI.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Method 1: Using ‘Data Source Settings’ (The Easy Way)

This is the most direct method and works perfectly when you’ve only changed the file’s location or name. Power BI has a specific menu for managing your data source paths.

Step 1: Open Data Source Settings In Power BI Desktop, go to the Home tab on the ribbon. Click on the Transform data dropdown menu and then select Data source settings.

Step 2: Select the Source to Change A dialog box will appear listing all the data sources used in your current report. Find and select the Excel file you want to replace.

Step 3: Change the Source Path With the source selected, click the Change Source… button at the bottom.

Step 4: Browse for the New File Another small window will pop up showing the current file path. Click the Browse… button and navigate to the new Excel file's location. Select it and click Open.

Step 5: Confirm and Refresh Click OK in the Change Source window, then Close the Data source settings window. Back in the main Power BI view, click the Refresh button on the Home ribbon. Power BI will now pull data from your new Excel file. If your file preparation was correct, everything should refresh without issues.

Method 2: Using the Power Query Editor (For More Control)

Sometimes you need more granular control, or the first method doesn't work as expected. In these cases, the Power Query Editor is your best friend. Every connection to a data source starts with a "Source" step in Power Query, and you can edit this step directly.

Step 1: Open the Power Query Editor On the Home tab of Power BI Desktop, click the main Transform data button. This will launch the Power Query Editor in a new window.

Step 2: Select the Query and Source Step On the left side of the Power Query Editor, under Queries, select the query connected to your Excel file. On the right, in the APPLIED STEPS pane, find the very first step, which is usually named Source. Click the small gear icon next to it.

This action will open the exact same dialog box you saw in Method 1, allowing you to browse and select the new file path. It’s just another way to get to the same place.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Using the Advanced Editor for Full Control

If you're comfortable looking at a bit of code, you can change the path directly in the underlying M code. This gives you the ultimate control and is great for understanding how Power BI works.

Step 1: Open the Advanced Editor With your Excel query selected, go to the Home tab in the Power Query ribbon and click Advanced Editor.

Step 2: Edit the File Path in the Code A window will pop up showing the M code for your query. The very first line defines the source. It will look something like this:

let
    Source = Excel.Workbook(File.Contents("C:\Old Reports\Old Folder\Finances.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
in
    Sheet1_Sheet

Simply replace the old file path inside the parentheses of File.Contents() with the new file path. Make sure to keep the quotes.

let
    Source = Excel.Workbook(File.Contents("C:\New Reports\Current Folder\Finances_New.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
in
    Sheet1_Sheet

Step 3: Click 'Done' and Apply After editing the path, click Done. Then, in the main Power Query Editor, click Close & Apply in the top-left corner to save your changes and refresh the report.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

The Pro Move: Replacing a Local File with a SharePoint File

Moving your data source from a local computer to a cloud location like SharePoint is one of the best upgrades you can make to your reporting workflow. It makes the data accessible to your team and is necessary for setting up scheduled refreshes in the Power BI Service.

The trick here is getting the correct path from SharePoint. Do not use the URL from your web browser’s address bar.

Step 1: Get the SharePoint File Path

  1. Navigate to the SharePoint document library where your file is located.
  2. Select the Excel file by clicking the circle next to its name (don't click the name itself).
  3. Click the Details pane icon (a circle with an 'i' in it) on the top right.
  4. Scroll down in the Details pane and find the Path. Click the Copy path icon.

Step 2: Update the Source in Power Query Now, go to the Power Query Editor and open the Advanced Editor for your query. You need to modify the Source line in your M code. You will change it from using File.Contents() to Web.Contents().

Your old code might look like this:

Source = Excel.Workbook(File.Contents("C:\Your\Local\Path\Data.xlsx"), null, true)

Change it to this, pasting the SharePoint path you copied. Important: Remove the ?web=1 part from the end of the URL if it's there.

Source = Excel.Workbook(Web.Contents("https://yourcompany.sharepoint.com/sites/YourSite/Shared%20Documents/Folder/Data.xlsx"), null, true)

Step 3: Edit Credentials After clicking Done, Power BI will likely prompt you for credentials since you're now connecting to a secure online location. Click Edit Credentials, select Organizational account from the left-hand menu, and sign in with your work account.

Troubleshooting Common Errors

Even with careful preparation, you might hit a snag. Here are a few common errors and how to fix them:

  • Error: "We couldn't find a file at..." This is an error with your file path. Double-check for typos, and ensure the path you entered is correct and that you have permission to access that location.
  • Error: "The column '[Column Name]' of the table wasn't found." This almost always means the header in your new Excel file does not exactly match the header from the old file. Open the Excel file and check for spelling errors, extra spaces, or changed column names.
  • Error: "We couldn't authenticate with the credentials provided." This is common when swapping to a SharePoint or OneDrive source. Go to Data source settings, find your data source, click Edit Permissions, and sign in again with your organizational account.

Final Thoughts

Swapping out an Excel data source in Power BI is a common task that becomes simple once you know where to look. By either using the straightforward Data Source Settings menu or the more powerful Advanced Editor in Power Query, you can easily point your reports to a new file location and keep your dashboards up-to-date and accurate.

Of course, managing static files is where many reporting headaches begin. At Graphed, we help you skip the manual process of downloading, saving, and swapping files altogether. Instead of pointing Power BI at an Excel file, we give you a way to connect directly to the apps you use every day - like Google Analytics, Shopify, HubSpot, or Salesforce. This way, your reports are built on live data from the start, update automatically, and you can focus on finding insights instead of fixing file paths.

Related Articles