Power BI – Remove Columns and Remove Other Columns

By: David Rohlfs

Introduction

Effectively shaping your data is essential in Power BI, and two of the most valuable tools for this purpose are Remove Columns and Remove Other Columns. These features empower you to refine your datasets by removing fields you no longer need or by keeping only those that are critical to your analysis. Knowing how to apply these functions appropriately will not only keep your data models clean and efficient but also make ongoing management far simpler. In this blog, we will go over when you should use these functions, some common problems you may encounter, and a walkthrough showing how to use these functions.

 

When to use it

The Remove Columns and Remove Other Columns functions in Power BI offer report builders’ significant control over data manipulation. Both functions serve to eliminate columns from queries within Microsoft Power BI’s Power Query environment, facilitating streamlined data management. Given Power BI’s capacity to process large volumes of data, efficient column removal is crucial.

The Remove Columns function serves as the primary tool for excluding unwanted fields from a query, whether those fields are obsolete or contain errors. Users simply select the fields to be removed and apply the function. The associated syntax is straightforward; it requires specifying only the columns designated for removal.

Table.RemoveColumns – PowerQuery M | Microsoft Learn

Similarly, the Remove Other Columns function enables users to retain only the selected columns, thereby removing all others from the query. This approach proves advantageous when working with extensive datasets containing surplus fields. Applying Remove Other Columns at the outset simplifies subsequent navigation and can potentially enhance performance. Alternatively, some users opt to employ this function at the conclusion of building a query to leverage benefits such as query folding.

Table.SelectColumns – PowerQuery M | Microsoft Learn

 

Common Problems

Remove Other Columns Removes Newly Added Fields

When using Remove Other Columns, make your actions clear. If the data source owner adds a new field, you won’t see it unless you whitelist it in that step. To include a new column, update the Remove Other Columns step or add the column to Applied Steps.
Column Changes Names

Whether it is Remove Columns or Remove Other Columns, a column name change will break your query. You will see a field missing error and what you will need to do is go into that query and redo, replace, fix that broken step or change the name back to what it was originally.

Performance Issues

The remove columns and remove other columns functions are not Delete buttons! In no way at all are you deleting data by using these functions in Power BI (Unless you have some type of writeback going on). What is really happening is that you added a step to take those fields out of the query, not deleting them. This means that they still load in, still do transformations before being removed, and still take up performance.

Having too many remove columns and remove other columns will ruin your refreshing performance. If you are working through your query and have more than two or three remove columns or remove other columns, you need to find a way to cut that number down. Remove Columns and Remove Other Columns aren’t super taxing on performance, but if your query is doing that every couple of steps, you are going to feel an impact.

Max Fields

I am not sure if there is a max number of fields for Table.RemoveColumns or Table.SelectColumns, but there is for Power BI. While I haven’t found any limits for Removing Columns in one action, Power BI has a maximum of 16,000 columns, effectively creating a max for these functions.
Data sources for the Power BI service – Power BI | Microsoft Learn

Missing Fields

In the syntax for both Table.RemoveColumns and Table.SelectColumns there is a Missing Field option. This is where you can specify that if a field is missing then null.
MissingField.Type – PowerQuery M | Microsoft Learn

 

Walkthrough

Remove Columns

In Power Query, select the columns that you would like to remove. You can do this with a control-select or with a shift-select. Then in the middle of the home ribbon you should see a remove columns button. Click on that.

 

Remove Other Columns

In Power Query, select the columns that you would like to keep. You can do this with a control-select or with a shift-select. Then in the middle of the home ribbon you should see a remove columns button with a dropdown, click on the dropdown and select Remove Other Columns.

 

Conclusion

Removing Columns and Removing Columns is a simple process in Power BI, but there are some good things to know when using the functions. If you skipped over the Common Problems section, it contains a lot of golden detail nuggets when working with these functions. If you’ve enjoyed this blog, there are a few related blogs linked below that may help you.

 

Links Related to This Blog:

Power BI – Remove Duplicates From Multiple Columns

Power BI – Cleaning Data by Fill Down and Remove Alternate Rows

Power BI – Row Specific Columns

Power BI – Remove Duplicates

Comments