Dynamic Column Selection in Power Query

If you have used Power Query for a while, you’d be familiar with the Remove Other Columns feature. This feature hard codes the names of the columns that you need and the rest are removed. Awesome!

But what if you want to select a few columns, may be based on a condition dependent on the column name. In other words if you are trying to make the column selection dynamic, this is the post for you.

 

Understand better with Video ?

Look at code for Remove Other Columns

= Table.SelectColumns(Source,{'Name', 'Address'})

It hard codes the names of the two columns : “Name”, “Address” which need to be dynamic

 

Now consider this data!

Since this data is converted into a table format (in excel) therefore names of two columns cannot be the same, that explains Address2 & 3 and Phone2. This would also happen automatically when the data is loaded into Power Query.

Nevertheless, what I want is to hold the first column (the first address, first phone, first email etc..) in my data and delete others.

The Problem is that Remove Other Columns is going to work perfectly fine on this data but what if I get two child columns: “Child” and “Child2”. In that case Remove Other Columns would fail to capture the new column (since it hard codes the names of the columns).

My query should rather, keep the first of the two “Child” columns. Let’s see how can we make the column selection dynamic.

 

Dynamic Column Selection Steps

Step 1 – After I load the Data in Power Query I write a new step in the formula bar

= Table.ColumnNames(Source)

This gives me a list of column names.

 

Step 2 – Now since you can only do so much with the list. We’ll need to convert the list into a table.

Right click on the header List >> To Table

 

Step 3 – You should now have a table, which gives you the full feature access to work with. Since we only want to keep the first columns (first address, first email etc..). I am going to check if the last letter of the column name is a number or not.

I’ll create a new column. Add Columns Tab >> Custom Column and write the following formula

= Text.End([Column1],1)

Text.End is the alternative to the RIGHT function in Excel. Take a look at the new Custom column created.

 

Step 4 – If I just convert the Custom column to the data type : number and remove the errors, I land up with the columns that I need. Perfect

 

Step 5 – Now I’ll convert “Column1” into a list and feed that into Table.RemoveColumns()

I write the following formula in the formula bar

= Table.RemoveColumns(Source,#'Removed Errors'[Column1])

Notice a few things about Table.RemoveColumns

• The first part is the table name from which we want to remove columns. Which is the Source step
• As a second part we need the list of columns to remove. Although #”Removed Errors” is also a table but writing [Column1] in the end makes it a list. Which is what we need!

Bingo we have the columns that we need but dynamic and not hard coded!

 

Alternative Solution

1. After you extract the last letter of the Column1 (your column names).
2. You can also choose to keep the errors the Custom column, this will give you the list of the columns to retain.
3. Now instead of writing Table.RemoveColumns, you’ll write Table.SelectColumns with the same inputs as before. Difference being that the list now contains the columns to retain not remove.

 

Key Learning!

The point of this example to is to show you..

1. The ability to extract the column names as a list using Table.Columns() and then convert it into a table to work with it further.
2. You can apply any sort of logic to select the column names and feed it again to Table.RemoveColumns() or Table.SelectColumns() to make your columns dynamic.

I hope you enjoyed this one

 

 

Some more Power Query Awesomeness!

• Dynamic Column Renaming
• Power Query Tip Card – Downloadable
• Running Total in Power Query
• Calculating Custom Fiscal Year and Qtr
• Age in Years and Months
• Filter Data with Multiple Conditions in Power Query