The Problem
If you are working with data where the source is uncontrollable, you may have experienced a situation in which you receive the "The column 'column name' of the table wasn't found" when a refresh is attempted.
Why Am I Getting This Error?
You will get this error when a column name changes or is removed altogether from the data source. In the background, Power Query is attempting to locate a specific column name that no longer exists.
Alien Tip: It's best to have a data source that is consistent with column names, but sometimes this can't be avoided. So, the next best thing we can do is have Power Query use the column numbers (indexes) rather that the names.
The Solution
The quickest solution is to go into the Power Query editor, locate the step that is causing the problem (most likely the "Changed Type" step), and remove the step completely.
Of course, this is bad data model design practice. We always want to define our data types. So instead of removing it, an immediate solution is to fix the formula.
In the formula bar, locate the column that changed and make the appropriate fix by either modifying the column name or removing the bracket section.
A Long Term Solution
In the Power Query editor, instead of using column names, we can use column numbers. This is possible with the Table.ColumnNames() function, which returns a list of column names. Lists are really powerful and can do magic in terms of making your Power Query code more dynamic. Read more about that in my blog about the Magic of Lists.
It's important to realize that it returns a list and not a table. Because it's a list, the values start at 0 instead of 1. So if we want to return the first column name from the list, we can do:
Table.ColumnNames(myTable){0}
The code we want to modify will be located in the "Changed Type" step under the "Applied Steps" section. I'll be modifying it in the advanced editor to make it look cleaner.
This is the standard, unedited code with the column names being used:
let
Source = Excel.Workbook(File.Contents(
"C:\Users\Brent\Sample Restaurant Data.xlsx"),
null, true),
menu_Table = Source{[Item="menu",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(menu_Table, {
{"MenuID", Int64.Type},
{"MenuName", type text},
{"Cost", type number},
{"Price", type number},
{"Margin", type number}
})
in
#"Changed Type"Within the #"Changed Type" variable, we are just going to replace the column names with the Table.ColumnNames() function, along with the column position:
let
Source = Excel.Workbook(File.Contents(
"C:\Users\Brent\Sample Restaurant Data.xlsx"),
null, true),
menu_Table = Source{[Item="menu",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(menu_Table, {
{Table.ColumnNames(menu_Table){0}, Int64.Type},
{Table.ColumnNames(menu_Table){1}, type text},
{Table.ColumnNames(menu_Table){2}, type number},
{Table.ColumnNames(menu_Table){3}, type number},
{Table.ColumnNames(menu_Table){4}, type number}
})
in
#"Changed Type"Click 'Done' and you're set - Nice!
Using a Variable for the List
If you want to make the code more efficient, we can create a variable for the column name list to reference inside the TransformColumnTypes() function. I'll use the following:
columnList =Table.ColumnNames(menu_Table)
let
Source = Excel.Workbook(File.Contents(
"C:\Users\Brent\Sample Restaurant Data.xlsx"),
null, true),
menu_Table = Source{[Item="menu",Kind="Table"]}[Data],
columnList = Table.ColumnNames(menu_Table),
#"Changed Type" = Table.TransformColumnTypes(menu_Table, {
{columnList {0}, Int64.Type},
{columnList {1}, type text},
{columnList {2}, type number},
{columnList {3}, type number},
{columnList {4}, type number}
})
in
#"Changed Type"
Using DAX Instead
You could also just remove the #"Changed Type" step altogether from the Power Query steps, and perform the data transformations using DAX in the Power BI data model. If the column name happens to change, the data will load successfully, and Power Query will not give you an error, but you will have to reapply the data type for each column that changed.
I hope this article helped! There are many functions in Power Query. If you know of any other way to accomplish this, please let me know in the comments! Cheers!
Building an Access Database? Check out my ebook on Amazon!
