The Antares Alien

Power BI & Game Dev Blog

  • Brent Jones

Power Query: Preventing "The column of the table wasn't found" error

Updated: Jul 24


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.

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!

If you are looking to improve your DAX skills, I would recommend Supercharge Power BI by Matt Allington:


Building an Access Database? Check out my ebook on Amazon!

#M #PowerQuery #Column #Name #List #Error #PowerBI #Not #Found

Support my Indie game!
Steam

Itch.Io

Cover Art - Main_Medium_With_Title_Cropp
Cover Art - Main_Large_With_Title.png