Include MissingField Argument with Table.TransformColumnTypes Function for Power Query

[cyfairmedicalpa]

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.

[alexander8]

I agree!!

[924409]

Agreed!

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

[ameer_shakour]

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]

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.

[jordy_moorman]

This function would be so great to have - for others wishing for the same, here's a good custom function to provide similar results until MSFT provides a solution:

Safely transforming column types in Power Query – XXL BI

[rick1]

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/

[user10456]

The documentation says this should work, but it isn't working yet https://learn.microsoft.com/en-us/powerquery-m/table-transformcolumntypes