Do you keep getting the error that states The column ______ of the table wasn’t found? It’s because the column names in the source data have changed. The thing is, if we can control the column names in our query, it is much less likely to break. There is a skill in writing queries that minimize the use of column names. One useful technique is to change column name by position in Power Query, and that’s what we are looking at in this post.
Table of contents
The column names problem
Many actions in Power Query hardcode column names into the M code. To demonstrate this, let’s look at a two common scenarios – Change Type and Move Columns.
The examples are based on the following data. The issue is the Forecast Jun-22 column, as when it refreshes in 1 months’ time it will be called the Forecast Jul-22.
Change Type – Change type may be applied by Power Query automatically depending on your default settings. This can even occur before we’ve had a chance to make any transformations.
= Table.TransformColumnTypes(
#"Promoted Headers",
{{"Item", type text}, {"Region", type text},
{"Forecast Jun-22", Int64.Type}}
)Move Columns – Moving columns is a frustrating transformation as it hard codes all the columns, even those which haven’t moved.
= Table.ReorderColumns(#"Promoted Headers",{"Item", "Forecast Jun-22", "Region"})Power Query assumes column names will be identical every time a query refreshes. However, if the column Forecast Jun-22 in the source file changes we get the column ‘Forecast Jun-22’ of the table wasn’t found error.
It’s unlikely that we can change the source file (these often come from other systems), so we need to find a Power Query solution to solve this.
Power Query solution
While there are a few Power Query solutions to deal with this scenario, we are only looking at one of these, changing column name by position. Instead of referring to columns by their name, we can refer to them by their number.
REMEMBER:
Power Query has a zero base. Therefore, the first column is 0, the second column is 1, and so on.
The strategy is to name columns before applying any other transformations. This gives us greater control, as we know every column name in the remainder of our query and in our DAX measures.
Power Query functions
For this, we will be using two Power Query functions Table.RenameColumns and Table.ColumnNames.
Let’s look at each of these in turn.
Table.RenameColumns function
The Table.RenameColumns function renames columns based on a list of pairs of text values. Within each pair is the “change from” name, followed by the “change to” name.
Syntax
Table.RenameColumns(table as table, renames as list, optional missingField as nullable number) as table- table: the name of the table to perform the transformation on. This is usually the name of the previous step.
- renames: the list of names to change to and from
- missingField: an optional parameter to determine what to do with missing names. For this parameter, the values can be either:
- MissingField.UseNull: if the name cannot be found, a new column is created using the new name and containing all null values.
- MissingField.Ignore: if the name cannot be found, no changes are made, and no errors are triggered.
- MissingField.Error: the default behavior of displaying an error message.
More info: https://docs.microsoft.com/en-us/powerquery-m/table-renamecolumns
Let’s see this function in action.
If we double-click the header and rename the column, the M code created will look like this:
= Table.RenameColumns(#"Promoted Headers",{{"Forecast Jun-22", "Forecast"}})
If the column Forecast Jun-22 exists, it will rename the column; otherwise, it will trigger the error.
We can add the additional Missing Field parameter into the code as shown below.
= Table.RenameColumns(#"Promoted Headers",{{"Forecast Jun-22", "Forecast", MissingField.Ignore}})Now, no error will be created even if the name does not exist.
This has been included for information. Avoiding the error in this way may be a solution in some scenarios. However, usually this doesn’t help us. The point of renaming the columns is to give us greater understanding or control. Avoiding the error achieves neither of these. Instead, it pushes the error later in the query.
Table.ColumnNames function
If we want the name of the nth column, we use the Table.ColumnNames function to return a list of names. Then, we simply select the nth column from the list.
Syntax
Table.ColumnNames(table as table) as list- table: the table from which to obtain the list of column names. Usually this will be the name of the previous step.
More info: https://docs.microsoft.com/en-us/powerquery-m/table-columnnames
Let’s look at a few examples of this function in action.
Example 1: Return all the column names
= Table.ColumnNames(#"Promoted Headers")This code returns a list of the column headers: {“Item”, “Region”, “Forecast Jun-22″}
Example 2: Returns column name in a specific position within the list
= Table.ColumnNames(#"Promoted Headers"){2}This code returns only the column header of the 3rd column: {“Forecast Jun-22″}
Nesting functions to change column name by position
Now we understand the two functions, it’s time to use them together. The Table.ColumnNames{n} function returns the name of the column from position n. This column name is then used in the Table.RenameColumns function.
This means we change this code:
= Table.RenameColumns(#"Promoted Headers",{{"Forecast Jun-22", "Forecast"}})Into this code:
= Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){2}, "Forecast"}})The biggest risks now are:
- the column number is not a valid column number (e.g., a negative number, or a number greater than the amount of columns).
- the column to be renamed does not remain in the same position
But in many scenarios, this simple technique is sufficient to avoid most issues.
Other Resources
Looking for more Power Query content? Check out these resources: