Skip to main content

Power BI

Needs Votes

Include MissingField Argument with Table.TransformColumnTypes Function for Power Query

Vote (136) Share
Greg's profile image

Greg on 22 Sep 2017 07:35:16

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.

Comments (6)
Greg's profile image Profile Picture

Rick de Groot on 13 Jun 2023 09:40:41

RE: Include MissingField Argument with Table.TransformColumnTypes Function for Power Query

Thanks greg, I love that idea. Currently the MissingField function are only used for: Record.RemoveFieldsRecord.RenameFieldsRecord.ReorderFieldsRecord.SelectFieldsRecord.TransformFieldsTable.FromRecordsTable.RemoveColumnsTable.RenameColumnsTable.ReorderColumnsTable.SelectColumnsTable.TransformColumnsSounds 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/

Greg's profile image Profile Picture

Jordy Moorman on 11 Nov 2022 16:43:29

RE: Include MissingField Argument with Table.TransformColumnTypes Function for Power Query

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

Greg's profile image Profile Picture

Arete Zhou on 08 Nov 2022 02:40:11

RE: Include MissingField Argument with Table.TransformColumnTypes Function for Power Query

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.

Greg's profile image Profile Picture

Ameer Shak on 14 Apr 2022 14:14:15

RE: Include MissingField Argument with Table.TransformColumnTypes Function for Power Query

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.

Greg's profile image Profile Picture

SDM SDM on 30 Mar 2022 18:08:30

RE: Include MissingField Argument with Table.TransformColumnTypes Function for Power Query

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

Greg's profile image Profile Picture

Power BI User on 06 Jul 2020 00:13:06

RE: Include MissingField Argument with Table.TransformColumnTypes Function for Power Query

I agree!!