cyfairmedicalpa's avatar
cyfairmedicalpa
New Member
8 years ago
Status:
Needs Votes

Include MissingField Argument with Table.TransformColumnTypes Function for Power Query

From what I can see (and it's not listed in the M function reference) the built in Table.TransformColumnTypes doesn't accept the MissingField argument like most of the other Table functions. This is extremely troublesome because if you are processing a dynamic data source, columns could come and go. It would also seemingly be beneficial to potentially pull this into the Power Query UI and possibly even set this as the default. The reason I suggest this is that In most scenarios that I come across, failing to transform a column type is not always something that is "mission critical". Especially when this is the default declarations that Power Query automatically inserts. (Perhaps default behavior for implementing this would be the automatically inserted declarations ignore missing fields and any explicit type changes after the fact do not ignore missing fields by default. This suggested change would also help new users as there really isn't an easy way to catch individual columns with try/otherwise since it's actually multiple transformations.
power bi

9 Comments

  • 924409's avatar
    924409
    New Member
    4 years ago

    Agreed!

    This would have saved hours of searching for alternatives for me.

  • ameer_shakour's avatar
    ameer_shakour
    New Member
    4 years ago

    PowerBi team, it has been 5 years. Let's do this already. This is a no-brainer. We are using dynamic data not static unless PowerBI wants to be known stable for its static capabilities only.

  • Arete_Zhou's avatar
    Arete_Zhou
    New Member
    3 years ago

    Refreshing will not fail, and it definitely will save PBI developers' life. After all, the business guys may don't want some columns after some periods.

  • rick1's avatar
    rick1
    New Member
    3 years ago

    Thanks greg, I love that idea. Currently the MissingField function are only used for:


    • Record.RemoveFields
    • Record.RenameFields
    • Record.ReorderFields
    • Record.SelectFields
    • Record.TransformFields
    • Table.FromRecords
    • Table.RemoveColumns
    • Table.RenameColumns
    • Table.ReorderColumns
    • Table.SelectColumns
    • Table.TransformColumns


    Sounds great to add Table.TransformColumnTypes to it.


    If at any time you're curious where numeration are used, I think you'll find this page useful: https://powerquery.how/missingfield-error/

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    10 months ago

    user10456 It works in Power BI Desktop version 2.143.1378.0 (May 2025) and later, for Excel users it will depend on your O365 version/channel (confirmed on Current and M365 Monthly). Here's a sample you can try. 

    let
    /* The July 2025 update to the documentation now mentions 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

     

Recent ideas