Power Query: Blanking the Blanks

Welcome to our Power Query blog. This week I look at one way to remove blank rows and columns.

The scenario is familiar: a huge Excel table comes in with lots of columns and rows, but in some cases a column or row is completely empty, and the table needs to be condensed. This may be easy to do by hand for small tables, but for large data sets it would be nice to be able to do this automatically, at the press of a button.

For my example, I have imported my Items table into Excel and this already has a few empty columns. I’ve also added a couple of blank rows:

From the ‘Data’ tab I go to the ‘Get and Transform’ section and choose the option ‘From Table’. I then get the following data in the Query Editor:

In the ‘Home’ tab, there is a section called ‘Reduce Rows’. The ‘Remove Rows’ option drops down in this section gives me several choices:

In this case I want to remove the blank rows, so I choose this.

A step appears in the ‘APPLIED STEPS’ telling me that it has ‘Removed Blank Rows’ and my row number has reduced. Now, for the columns:

Even though there is a ‘Remove Columns’ option in the ‘Manage Columns’ section, there is no option to remove blank columns. Instead, I need to transform my data first.

In the ‘Transform’ tab, I have the option to ‘Transpose’ which will treat columns as rows and vice versa. I choose this.

Now I can remove my blank rows again.

My ‘rows’ have decreased from 17 to 10, so I can transpose again to reverse my columns and rows to their original configuration.

I can now close and load to my spreadsheet. One point to note – there is a reason why my column names are generic: transposing will lose the column names. Therefore, there is some work required to rename the columns, particularly as the blank ones will have disappeared.

Next time I will look at another way to remove blank rows and columns using the unpivot function. And this time I can keep my column names…

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here.