Tag Archives: Ascription

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
let 
  // in real life, comes from a database
  Data = #table(
          {"Amount", "TransactionID"}, 
          {
              {100.25, 1},
              {32.99, 2}
          }
        )
in
        Data

// MyNicerTable
let
  Source = BaseDataSet, 
  Result = Value.ReplaceType(Source, type table [Amount = Currency.Type, TransactionID = Int64.Type])
in
  Result
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!

Continue reading