Removing columns

Overview

‘M’ code has two main functions for selecting and removing columns: Table.RemoveColumns, Table.SelectColumns

Both functions accept a third argument, which can be MissingField.Ignore, or MissingField.UseNull.

MissingField.Ignore ignores the missing column, while MissingField.UseNull keeps the column name and fills it with nulls. MissingField.UseNull is more practical than its sibling MissingField.Ignore, especially when used with Table.SelectColumns, as it allows you to ensure the selected column names are included in the end result.

Generally speaking, when selecting columns, to reduce the risk of an ETL refresh failure, it is always better to focus on the columns you want to keep, than on the ones you want to remove.

Removing columns based on their position can also be better than referencing them by name.

Suppose you have data like follows:

In this article, we explain different possible methods of removing and/or selecting columns from such data:

Selecting or removing columns based on their position

The following code will remove the first column:

    = Table.RemoveColumns(Source, List.First(Table.ColumnNames(Source)))

The following code will remove the last column of the table:

    = Table.RemoveColumns(Source, List.Last(Table.ColumnNames(Source), 1))

The following will keep the 2nd and 3rd columns only:

    = Table.SelectColumns(Source, List.Range(Table.ColumnNames(Source), 1, 2))

The List.Range function receives a list as the first argument, then a zero based offset, and then count of items to return.

If you need to select 2 non-adjacent columns, say “Color”, and “Revenue”, you would use the following code:

    = Table.SelectColumns(Source, {Table.ColumnNames(Source){2}, Table.ColumnNames(Source){5}})

Selecting or removing columns based on their name

The following uses the List.Select function to remove columns that contain the substring “Supp”:

    = Table.RemoveColumns(Source, List.Select(Table.ColumnNames(Source), each Text.Contains(_, "Supp")))

The same results using Table.SelectColumns and the reverse selection logic:

    = Table.SelectColumns(Source, List.Select(Table.ColumnNames(Source), each not Text.Contains(_, "Supp")))