3 Easy Steps to Manage your Data Fields

Want to control which data fields to keep in Power Query when removing other columns?

When using the ‘Remove Other Columns’ transformation in Power Query (‘Get & Transform’ in Excel 2016+) the query editor hard-codes the remaining column names in the Advanced Editor. This is fine if your database structure is supposed to remain static. However, if you wish to add additional field at some later point, you would have to do it manually in the ‘Advanced Editor’, which may be inconvenient. In this situation, it would help having an external list of field names, which you can edit and let the query keep only those appearing on the list.

Below are 3 easy steps on how you can manage your data fields in an external list:

To start with, let’s look at the following table (named tblSource) into Power Query, remove all the fields apart from “Full Name” and “Address”.

This would generate the following line in the Advanced Editor:

#”Removed Other Columns” = Table.SelectColumns(Source,{“Full Name”, “Address”})

The field names {“Full Name”, “Address”} in the ‘curly brackets’ is a ‘list’ for the purposes of Power Query. We will have to replace it with a variable.

Now, let’s follow the steps to make the fields flexible:

Step 1. Create a table in an Excel sheet as shown below, and name it as ‘tblFieldsToKeep’.

Our table contains an extra field for “Phone Number”, which we wish to add to the final query result.

Step 2. Copy the following line into the clipboard and paste into your source table query before the ‘Removed Other Columns’ line.

FieldsToKeep = Excel.CurrentWorkbook(){[Name="tblFieldsToKeep"]}[Content][Fields to Keep]

Here we have read the table contents into Power Query and converted the column “Fields to Keep” into type ‘list’. The list is stored in the variable ‘FieldsToKeep’.

Step 3. Replace the field names in the ‘Removed Other Columns’ line with the variable, so the code looks as follows:

#"Removed Other Columns" = Table.SelectColumns(Source,FieldsToKeep)

At this point, our job is done. The new ‘Phone Number’ column will appear in the resulting table. If you add an extra field name to the list, it will appear after the query is refreshed (off-course, as long as it exists in the source data).

Here is how the resulting query will look:

let
   Source = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content],
   FieldsToKeep = Excel.CurrentWorkbook(){[Name="tblFieldsToKeep"]}[Content][Fields to Keep],
   #"Removed Other Columns" = Table.SelectColumns(Source,FieldsToKeep)
in
   #"Removed Other Columns"

You may ask, though, what if you had to assign a specific type to each of the fields? The resulting line in the advanced editor would also contain hard coded values looking as follows:

#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Full Name", type text}, {"Address", type text}, {"Phone Number", type text}})