Power Query: Dynamic Removals
4 March 2020
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
My data has headers, so I accept the suggested options in the dialog.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I select the columns I want to remove, keeping CTRL pressed, and right-click to remove columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I want to check the M code generated for this step.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
The M code generated is
= Table.RemoveColumns(#"Changed Type",{"Card", "Cash", "Mike"})
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
The step I am going to add will create a list of my column names.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
The M code I am using is:
= Table.ColumnNames(Source)
I also used Table.ColumnNames in Power Query: Name or Number, when I was renaming columns using their position instead of their column names.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I accept the defaults and create my table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I want to filter my table so that I only have the columns I want to remove.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I click OK to leave the columns I want to remove.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image12.png/a1537847463e660a31158c8032525438.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image13.png/917da985be13220165c8d2823e95344f.jpg)
The M code I have used is:
= Table.RemoveColumns(Source,#"Filtered Rows"[Column1])
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
I have added another column, Cheque, to pay for a hotel bill. I refresh the query to see the results.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/170/image16.png/d082e3477129350b8a2a589156028e63.jpg)
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!