Power Query: Table.TransformColumnTypes Now Supports MissingField

[m_dekorte]

If you’ve worked with Power Query in Power BI or Excel, you’ve used Table.TransformColumnTypes. It’s the most common function, and helps us to assign data types to our columns. In May 2025, Table.TransformColumnTypes got an important and unique update: a MissingField type can now be provided within an Options record.

Using a record for Options within a function isn’t unusual in M. What’s unique is that Table.TransformColumnTypes is the only function where a MissingField type is included inside an Options record. In all other M functions that accept a MissingField type, it’s a separate parameter. For example, here's a list of functions with a MissingField type:

• Tables:

  • Table.FromRecords

  • Table.RemoveColumns

  • Table.RenameColumns

  • Table.ReorderColumns

  • Table.SelectColumns

  • Table.TransformColumns

  • Table.TransformColumnTypes (the update we’re discussing!)

• Records:

  • Record.RemoveFields

  • Record.RenameFields

  • Record.ReorderFields

  • Record.SelectFields

  • Record.TransformFields

So, while MissingField support isn’t new, inside an Options record of Table.TransformColumnTypes it is.

Check Your Version

It is available from Power BI Desktop version 2.143.1378.0 (May 2025) onwards. To quickly see if it's supported in your version/channel of Excel, follow these steps, (1) create a new blank query, (2) paste the following expression into the formula bar and (3) hit enter. If supported, you’ll find it in the documentation.

= Table.TransformColumnTypes

Practical Examples

No more elaborate workarounds! Here’s a working demo that illustrates how it works.

let
    /* May 2025 update: a MissingField type for Table.TransformColumnTypes */
    /* Here's sample data, a table with two columns: Date and Value */
    Source = #table(
        type table [Date = date, Value = number],
        {
            {#date(2024, 3, 12), 0.24368},
            {#date(2024, 5, 30), 0.03556},
            {#date(2023, 12, 14), 0.3834}
        }
    ),

    /* Attempt to change column types. */
    /* Note: "Customer ID" does not exist in the Source table, */
    /* so this step fails. */
    ChType_Default = Table.TransformColumnTypes(
        Source,
        {
            {"Date", type text}, 
            {"Customer ID", Int64.Type}, 
            {"Value", Percentage.Type}
        }
    ),

    /* Transform column types 3rd parameter accepts a Culture tag as text; "de-DE */
    /* Culture affects formatting/interpretation of values such as numbers and dates */
    ChType_Culture = Table.TransformColumnTypes(
        Source,
        {
            {"Date", type text}, 
            {"Value", Percentage.Type}
        },
        "de-DE"
    ),

    /* 3rd Parameter now also accepts a Record that may include a Culture and/ or MissingField. */
    /* MissingField.UseNull > Adds missing columns to the output containing null values. */
    /* In this case, "Customer ID" is added, filled with nulls. */
    ChType_RecordA = Table.TransformColumnTypes(
        Source,
        {
            {"Date", type text}, 
            {"Customer ID", Int64.Type}, 
            {"Value", Percentage.Type}
        },
        [Culture = "de-DE", MissingField = MissingField.UseNull]
    ),

    /* MissingField.Ignore > Ignores columns that don’t exist in the Source table, */
    /* preventing errors by skipping any missing columns. */
    ChType_RecordB = Table.TransformColumnTypes(
        Source,
        {
            {"Date", type text}, 
            {"Customer ID", Int64.Type}, 
            {"Value", Percentage.Type}
        },
        [Culture = "de-DE", MissingField = MissingField.Ignore]
    )
in
    ChType_RecordB

Before May 2025, Table.TransformColumnTypes would break a query if it listed a column that didn’t exist. Now, with MissingField.UseNull and MissingField.Ignore, you can decide how to handle missing columns: fill them with nulls or skip them entirely. It’s a small addition, but one that makes a huge difference in building robust, future-proof queries.

Have fun trying it out!