Power Query: Dynamic Removals

Welcome to our Power Query blog. This week, I look at how to remove columns without referencing column names.

John, my reliable imaginary salesperson, has been filling in data again. This time, he has added a few extra columns to his data.

John has been keeping track of how the expenses have been paid for, but I don’t need this information. I will extract the data into Power Query. To do this, I use ‘From Table’ on the ‘Get & Transform’ section of the ‘Data’ tab.

My data has headers, so I accept the suggested options in the dialog.

I select the columns I want to remove, keeping CTRL pressed, and right-click to remove columns.

I want to check the M code generated for this step.

The M code generated is

The problem with this, is that John will come up with other column names next time.  I need a more dynamic way of choosing which columns to remove.  To do this, I go back to my initial ‘Source’ step and delete the other steps.  I will add a new step using the fx button next to the Formula bar.

The step I am going to add will create a list of my column names.

The M code I am using is:

I also used Table.ColumnNames in Power Query: Name or Number, when I was renaming columns using their position instead of their column names.

I have a list of my column names. I want to convert this to a table using the ‘Convert to Table’ option on the ‘List Tools, Transform’ tab.

I accept the defaults and create my table.

I want to filter my table so that I only have the columns I want to remove.

I click OK to leave the columns I want to remove.

I need to reference this table when I remove columns. I add a new step that will take the source and remove the columns in this table.

The M code I have used is:

This removes the other columns without referencing their names. I can check that this will work if John adds other columns. I ‘Close & Load’ my data first, and then add another expense to John’s table:

I have added another column, Cheque, to pay for a hotel bill. I refresh the query to see the results.

The hotel expense has been added, but new column Cheque is not shown. It works!

Come back next time for more ways to use Power Query!