Are you a column chooser or remover?

Did you know there’s potential repercussions for choosing one over the other in Power Query?

When looking to reduce query sizes we often dive straight for the Remove Columns or Remove Other Columns transformations in the Power Query UI.

But which should you use? Does it even matter?

The answer is yes – there’s a huge downside to using Remove Columns over Remove Other Columns or Choose Columns, and its all down to query dependencies.

Let’s say we have a simple Customer table, with two columns: Customer ID, Customer Name. We want to keep the Customer IDs, but remove the Customer names.

When you use Remove Columns, the produced M code in Advanced Editor looks like:

Query A: Table.RemoveColumns(Source,{“Customer Name”})

When you use Remove Other Columns or Choose Columns, the M code instead looks like:

Query B: Table.SelectColumns(Source,{“Customer ID”})

Notice that in the first query, we have created a dependency on a column that we don’t want (Customer Name), whereas in the second query, there is a dependency on a column that we do want (Customer ID).

This means that in certain scenarios, the queries will behave differently (even though they achieve the same goal!):

• If Customer Name is removed from the source
  • Query A will break
  • Query B will not break
• If Customer ID is removed from the source
  • Query B will break
  • Query A will not break
• If a new column is introduced to the source
  • Query A will add the column to the table
  • Query B will ignore it

It is therefore worth keeping this in mind when deciding which option to use!

In most cases, I would use Select Columns or Remove Other Columns because:

• I don’t want the query to break if a column I don’t want is removed from source
• I want the query to break and tell me if a column I want is removed from source
• I want control over columns that are added to the query in case they break PII rules or create errors further down the query steps

You may have scenarios where you want the reverse of these dependencies, in which case you should opt for Remove Columns – use it wisely!