Refresh error from changing column names in data source

You might have faced a refresh error when a column name gets changed in data source, it happens when column name gets changed in data source.

So, whenever you do refresh your Power BI model returns an error as shown below.

In this situation, you can quickly go to power query editor and change the column name with the new name that is coming from data source.

But what happens, if you are not sure about that this column name is not going to be changed again in future. But you are sure on that columns order data source file is never going to be changed.

For such a scenario, lets see an example which demonstrate how to fix such refresh error which occurs due to column name changed in data source.

As you can see, here we have an excel file as a data source which contains Sales data.

Lets import this file into Power BI and create a simple report which displays sales by month, year as you can see below.

Now, you are asked to change the column Month Name as Month in data model.

For this you go to Power Query Editor and under Transform tab double click on Month Name header.

Now you change the column name as Month then under Home tab, click on Close & Apply button to save the changes.

Now you can see column Month Name is changed to Month that you can quickly check in fields pane.

So far everything is going good, now assume that due to some error or for any reason name of the column Month Name gets changed in data source.

Lets say, it is coming now as Mnth which was supposed to be as Month Name.

As you can see, now we have changed the column Month Name to Mnth in excel file and saved the excel file.

Now, lets refresh the Power BI model and see what happens.

And you can see, it gives an error that ‘The column Month Name of the table was not found’

The reason for getting this error is, if you remember that we changed the column Month Name to Month in Power Query Editor and for that Power Query has saved a transformation steps.

So, Every time when you refresh the Power BI model, Power Query repeats all the transformation steps that you have made in you Power BI Model. During this process it does not get a Month Name column in data model that’s why it returns an error.

Well, when you open Power Query Editor you may ended up getting same error again as shown below.

Lets fix this error, but before that make sure you have enabled your Formula Bar.

Other wise you can enable this, Go to Power Query Editor then under View tab, check Formula Bar.

You can see, the M query code which shows that column Month Name is changed as Month.

Lets fix the error, here we replace the Month Name with column order, or you can say by column positions in excel file.

You can see, in excel file order of Month Name column is 2. Lets replace text “Month Name” with following M function.

Table.ColumnNames(#"Promoted Headers"){1}

Here we have referenced the column by it’s position as 1 in curly bracket {1} because the list start with index 0.

You will notice that as soon as you modify the M query, an error is removed automatically.

Now, click on Close & Apply button to save the changes.

Lets confirm this by making changes in column name again in excel file.

This time we change the Mnth column to #Mnth in excel file and save the file then refresh the Power BI file and this time it should be refreshing without any refresh error.

You can see, model is being refreshed without any error.

 

You can also see visuals displaying data perfectly.

So using this way, you can fix refresh error due to column name change in data source. But make sure that column order should not be changing in data source then you are good to go with this technique.

Also Read..

Create Index column using Power Query

Merge Queries

Append Queries

Import text using examples feature Power BI

Pivot columns – turn rows into columns

Unpivot columns

Split Columns By Positions