Home ┬╗ Refresh error from changing column names in data source

Refresh error from changing column names in data source

You may have faced a refresh error when a column name is changed in data source, it happens in such scenario when you have renamed any column in power query and later for any reason that column name is get changed in data source.

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




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

But what happens, if you are not sure about that this column name is not going to be change again in future. But can surely say that the columns order data source file is not going to be change anyhow.

For such 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 column’s 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, or in data model.

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




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 of getting error is that, 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



 199 total views,  6 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.