Value.ReplaceType & Table Column Renames (Bug Warning!)

, , , , ,

If you work in the advanced realm of ascribing types, there are a couple interesting behaviors to be aware of related to table column renames (including a bug!).

Positional, Not Name-Based

Imagine you want to set column type claims on a table, so you create a query that uses Value.ReplaceType to ascribe an appropriate new table type.

// BaseDataSet
  // in real life, comes from a database
  Data = #table(
          {"Amount", "TransactionID"}, 
              {100.25, 1},
              {32.99, 2}

// MyNicerTable
  Source = BaseDataSet, 
  Result = Value.ReplaceType(Source, type table [Amount = Currency.Type, TransactionID = Int64.Type])
Query output:
| Amount | TransactionID |
| 100.25 | 1 |
| 32.99 | 2 |

So far, so good.

Later on, someone decides that the ID column should be moved to be leftmost, so they reorder columns by editing BaseDataSet. However, they don’t touch your MyNicerTable query with its Value.ReplaceType code. Look closely at what that expression now outputs:

Query output showing column names/types swapped

The column that contains transaction IDs is now named “Amount” and typed Currency.Type. Similarly, “Amount” values now show up under the column name “TransactionID” which is typed as whole number. Ouch!

What happened?

Key Fact: When ascribing a table type, the new type’s column details are applied positionally, not by name.

During type ascription, the table’s existing column names are irrelevant. The new type’s details are not matched to table columns based on name. Instead, the details of the type’s first column are applied to the first column in the table, the type’s second column to the second column in the table, and so forth—regardless of how columns were named in the source table.

In our example, the type being ascribed was not updated to reflect the column reordering. After that change in order, the new type’s column details end up being applied to what we humans would consider the wrong columns—though, from the technical perspective, this is the expected behavior.

Moral of the Lesson #1: If columns are reordered upstream of a table type ascription, the type being ascribed must also be updated to reflect the new column ordering.

Ascription Renames…At Least, in Theory

Looking at the last example’s output, we see that type ascription can be used not only to assign type claims to columns but also to rename columns.

At least, that’s the theory.

Per the language specification, this theory is correct. However, a bug in the flagship Power Query implementation (i.e. the Power Query that ships with Microsoft Power BI and Microsoft Excel) does not ascribe column renames correctly. It only partially applies the ascribed rename, which leads to each column sometimes appearing under its new name and sometimes under its old. Not good.

Below, Table.Schema and the SomeTable[ColumnName]-style reference are aware of the ascribed rename, while Table.SelectRows still sees the column as having its old name.

  Data = #table({"A"}, {{1}}),
  WithNewType = Value.ReplaceType(Data, type table [Z = number])
  Table.Schema(WithNewType) // reports the column name as Z
  // List.Sum(WithNewType[Z]) // works, returning 1
  // Table.SelectRows(WithNewType, each [Z] = 1) // doesn't work, even though the column is now named Z
  // Table.SelectRows(WithNewType, each [A] = 1) // instead, new column Z can still be referenced by its old name of A

There’s the chance that one day this bug will be fixed, but that’s unlikely to happen anytime soon.

Moral of the Lesson #2: While ascription theoretically can be used to rename columns, to avoid bad things happening, do not use it for this purpose in real life.

The Bug Bites

Let’s go back to our first example and think about how this bug affects it. That example outputs a table with column names + types inadvertently swapped due to a mismatch in column order between the new type and the table’s columns.

Let’s use that table and see what happens….

// outputs a single row
// filters using *old* column names, so returns what the original table considered the TransactionID = 1 row, even though new the table that's output shows the TransactionID = 100.25 (i.e. the old Amount value)
Table.SelectRows(MyNicerTable, each ([TransactionID] = 1)) 
Query output showing Table.SelectRows filtering on "TransactionID = 1" but outputting row with "TransactionID = 100.25"
// errors - no matching row found
// filters using *new* column names, so looks for 1 in what the new table calls TransactionID, which the old table called column Amount
Shows item selection on [TransactionID=1] resulting in a 'key didn't match any rows in the table' error

Confusing? Can you see how this bug could lead to a query producing strange results that could take a bit of time to debug? Ouch!

Summing It Up

Table type ascription is, indeed, an advanced concept. With its use comes the need to apply it carefully—and to update it whenever upstream column reorders (or renames) occur. It is vital that the order of columns in the being ascribed type align exactly with the existing table’s column order.

2 thoughts on “Value.ReplaceType & Table Column Renames (Bug Warning!)

  1. sam

    @Ben – I cant believe this… Are MS aware of this issue ?
    The time I use Value.ReplaceType is to “undo” the effect of change of data type of certain columns to “any” after doing a Table.ReplaceValue operation so
    = Value.ReplaceType(Table.ReplaceValue(mPreviousStep,
    each [HRS],each [HRS]/8,Replacer.ReplaceValue,{"HRS"}),Value.Type(mPreviousStep))



Leave a Reply

Your email address will not be published. Required fields are marked *