Data Management: M Scripting - Preventing "The column of the table wasn't found" error message.

August 13, 2018

 

 

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.

 

 

The source of this error is in the Power Query M script. Power Query is attempting to located a specific column name that no longer exists. It's trying to automatically detect the data type. You will get this error when a column name changes or is removed altogether.

 

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

 

We can do this with the Table.ColumnNames() function. This returns a list of column names.

 

It's important to realize that it returns a list and not a table. Since 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!

 

 

Please reload

About the Author

Brent Jones loves spending time with his wife and trying out new activities together.  They traveled as digital nomads to many countries in South America, Europe and Asia. He can also speak Japanese!

Our Recent Posts

Please reload

Tags

Address

7000 Central Pkwy #1100, Atlanta GA 30328

Follow

  • Twitter - Black Circle
  • Black Pinterest Icon

©2019 BY ANTARES ANLYTICS. PROUDLY CREATED WITH WIX.COM