When working with Import Mode in Power BI, you should look at the size of your data model and make sure that it’s not bigger than necessary. There are many techniques you can apply to optimize your model, but there are two extremely simple transformation steps, that have the biggest impact on your model size. You can either reduce the number of rows (by filtering for example) or reduce the number of columns by removing them from dataset.

While this is not article about details of Power BI’s storage engine, it is important to know that Power BI stores the data column by column instead of row by row. That is why number of columns is more critical for the model size than the number of rows.

That’s why you should definitely consider removing unnecessary columns from the model. Today, I’ll walk you through the available options in the Power Query UI and make a case for why each is important.

Scope of the article

Before we start

If you followed the Introduction to Power Query Articles you know already that I am a huge advocate for enabling the Formula Bar, regardless of your coding experience or aspirations. For the purpose of this discussion Formula Bar must be enabled, so it is easier to understand the difference between mentioned functions and what are the implications of calling them. To enable Formula Bar, go to View tab in Ribbon and select Formula Bar checkbox:

 

 

Closer look at available functions

I mentioned three functions that will be covered in this article. All of them are available to you in Home tab of the Ribbon, in Manage Columns section.

You will find here an option to Choose Columns you need. When you expand available options for Remove Columns you will find a function with the same name and Remove Other Columns. Last two options are also conveniently available to you at the column level, when you right-click on column in the data preview:

 

 

How does each of these functions work? Let’s find out.

 

Choose Columns

This option is probably the most straight forward and the easiest to use, especially in scenario where you deal with a lot of columns and want to select only a few of them. When you use the function, a small window pops up:

1. List of all columns available at that stage.
2. Select All Columns – you can have all columns selected and unselect a few if needed or unselect all of them and select a few. Either way, this function allows you to choose columns as quick as possible.
3. Search Bar – when you have a lot of columns it makes it very easy to find columns you want to keep.
4. Sort Columns – very often overlooked feature. Not only you can select the columns you need but also sort them alphabetically. It helps to work in Data Preview in case you have a lot of columns.

Of course, the result of the function is that it keeps the columns that are selected. For the purpose of further discussion, let’s have a look at the code generated for this step in Formula Bar:

 

Remove Columns

Whether we use this function in Ribbon or directly on column level it doesn’t really matter. In both scenarios you must select columns that you want to remove in Data Preview. To select multiple columns, you must left click on each of them while holding Ctrl key. Selecting two columns while holding Shift key will select all the columns between them. You can also mix the keys to select few individual columns and add range of columns to the selection, by holding both Ctrl + Shift, it looks like in below picture:

Once all the columns that you want to remove are selected, sue the Remove Columns function to get rid of them. Let’s look at the code generated by this action:

There is one interesting behavior while using Remove Other Columns functions, that could prove to be useful to you. When you are selecting columns to keep, you can click on them in an order that you would like to see them. Power Query is smart enough to understand what you are doing, and once your columns are selected, they are also sorted in the order of your clicks:

Maybe this is not revolutionary feature, but I am pretty sure you can make good use of it, and not only reduce the model size but also organize the columns to make them easier to work with, all in a single transformation step.

 

Explaining the title

It’s fair to say that this is right about the time to finally explain the title of this article. We are talking about three different functions (Choose, Remove and Remove Other Columns), yet the title says two (and a half). We technically have three functions, but from the code perspective, we have only two (average is two and a half :)). You probably noticed that code generated for Choose Columns and Remove Other Columns functions is the same. You still can decide which one you like better or use them interchangeably depending on the need, but it is important to know, that code wise they are the same, and Remove Columns function is different. Why is this important?

 

Functions available for us in User Interface aim to make our life a bit easier, but they are still running the code behind the scenes. If you are not careful, the code can simply break, even in our case where the code generated by all the mentioned functions is extremely easy.

• Choose Columns and Remove Other Columns – calls Table.SelectColumns function accepting two arguments. First is the table that function will work on. The name you see here is the name of the last step in Applied Steps list (the result of transformation step is a table in a given stage of transformation). Second argument is a list of columns that you want to keep.
• Remove Columns – calls Table.RemoveColumns function. It also accepts two arguments. First again is the table like discussed above. Second argument is the list of columns that you want to remove.

Both functions work on list of columns as an argument. What happens when one of the columns is missing in the dataset?

Luckily, the error message is very easy to understand. Yet, you don’t want to see any errors in your solution. Especially when it’s shipped to other teams, and you want to focus on next work instead of keep fixing existing ones. From this extremely easy code we can draw the following conclusion: