Search
  • Marek Vavrovic

Data Refresh - Expression.Error column not found

Changed column names are frequent reason why the query breaks. This example works very nice for Excel source tables. We must do it in the step after PROMOTED HEADERS. This Excel table serve as a model.

I have loaded my table into Power BI and want to make sure that after some users change the column names the data will be still loaded correctly. I will show you an example how to do it with multiple columns not just with one column. But anyway, I need to keep the number of columns in some reasonable figure, can’t show you how to do it with 50 columns. But the process is very similar.


After the table got loaded into Power BI, click on Promoted Headers in the APPLIED STEPS section and go to Advanced Editor. We are going to create a function which takes Promoted Headers step as an argument and index which starts from 0 and represents columns. You can use any name for your function. Promoted Headers works similar as LIST or ENUM in other programming languages. (C#,Visual Basic...etc)


KissMyAss function returns the names of the headers. Holds a list of column names coming from Excel table.


If you click on the function in the APPLIED STEPS section, you can see the values it holds. {0} refer to the first header in our list. If you change the index number function will refer to different header.


Now click on the Changed Type in the APPLIED STEPS section and insert the function.

USE

Table.ColumnNames(#"Promoted Headers"){0} for "Locations[a]"

Table.ColumnNames(#"Promoted Headers"){1} for "Cases[b]"

Replace all 4 hardcoded column names within the function using correct index value{}.


After you finish it should look like this:


In between I’ve applied couple of new steps and correction on the table. I’ve removed one column. You don’t need to use this function in that step. But I’ve also renamed the columns and the function should be use for this step. Now repeat the process for RENAMED COLUMNS step.


Result will look like this. I’ve also marked the steps where I used the function with a red circle [RENAMED COLUMNS, CHANGED TYPE].


Don’t forget to SAVE any changes in Excel before you start to load the data. Otherwise it won’t appear in Power BI. If you have any question, do not contact me. Instead of that keep practicing!

0 views

©2020 by MaVa Analytics. Proudly created with Wix.com