How to resolve error or exception while refreshing data in Power BI desktop for Excel data source

Today we are going to discuss a very common issue in Power BI and that is how to resolve error/exception while refreshing data in Power BI desktop for Excel data source.

Refreshing data is always very important in Power BI. Whenever we refresh our data all changes got updated in our Power BI workbook too. But there are few scenarios where we face some exception or error while refresh Power BI desktop. Today we are going to discuss those scenarios for Excel data source:

1. For deleted columns in Excel file
2. For changed column header name in Excel file
3. For changed Sheet name in Excel file
4. For changed Excel file name
5. For change in path /location

Let’s see how we can fix these issues:

We have a sample dataset with 6 columns: ( Orderdate, Item, Region, Cost, Unit, Total)

Excel file name: Dataset1

Sheet name: Sheet1

Load this file in Power BI desktop:

Create Bar chart for Item, Unit and Cost:

To check Refresh functionality, let’s change Item name “Copy” to “Copy (Change name)”

Click on Refresh option:

Item name is changed:

For deleted columns in Excel file:

Delete Region Column and change Item name for copy (same as it was earlier):

Click on Refresh:

It will show an error and Refresh is failed:

Now go to Power Query Editor:

Steps present under Applied Steps.

Delete Applied steps except Source:

Click on Table hyperlink:

New Updated Excel file is uploaded without any error message.

Close and Save:

Item Name is changed and Region field is deleted.

For changed Column header name

Change Column header name in Excel file:

Go to Power BI Desktop and Refresh>

Open Power Query Editor:

Delete all applied steps after Source > open Source to edit

Click on Table hyper link

Updated Column header name is updated:

Apply and Save.

For changed Sheet name in Excel file

Go to Excel File and change Sheet name from Sheet1 to Order Summary:

Refresh:

It will give an error message.

Open Edit query:

Delete steps after Navigation step.

Open Navigation step to edit > Select new Sheet name > click on OK

Excel sheet is refreshed without any error message.

Apply and Save

For changed Excel file name and change in path /location:

Change Excel file name from Dataset1 to Dataset2

Now refresh Power BI desktop:

It will give an error message. We can resolve this problem from two places.

One is from Power Query Editor and other one is from Data Source settings:

From Power Query Editor:

Delete all steps after Source > Open Source to edit

Browse correct file name or directly change file name in path.

From Data Source Setting:

Open Data Source Settings

Select Change source

Browse correct file name and close.

Same we can do when path or location changed for Excel file.