Automatically remove empty columns and rows from a table in Excel using Power Query


In today’s tutorial we will solve a very common challenge in Excel. You start with an Excel table and you wish to condense it by removing all the columns and/or rows which are entirely empty, and usually you don’t have prior knowledge which columns and rows are empty.

Remove empty columns and rows from a table in Excel using Power Query

For example, In the screenshot above, we would need to remove all the empty rows and columns that are highlighted in yellow.

You can now say, “Oh, it’s easy, I can manually remove the empty rows and columns, what’s the big deal?”, and to answer you, I would add these challenges:

  1. The table is too big to scroll down or right and seek for the empty values.
  2. The table’s size can change.
  3. You need to import the table and condensed it on an ongoing basis.
  4. You don’t want to mess with VBA (I will show you a better way to do it, anyway).

To resolve our challenge, We’ll use Power Query / Get & Transform in Excel (You can apply the same solution in Power BI Desktop). What is Power Query? Learn more here.

Ready to start?

Subscribe to DataChant to get today’s sample workbook with the included solution.

Subscribe To DataChant

Join our mailing list to receive exclusive content that was used in the preparation of this article.

Thank you for subscribing to DataChant. An email will be sent to you shortly with more details.

Update: This tutorial includes four techniques to clean empty rows and columns: In the first part, we will show you the simplest way (Thank you Maxim for suggesting this technique).

In the second part, we will apply a function that cleans all of the empty columns. While you may prefer the first technique, if you are an advanced user, and are familiar with Power Query formula language (M), you may find there a new and useful way to use List.Accumulate. In the third part, we will apply the same function on the transposed table to clean the rows. Finally, in the fourth part, we will learn a neat trick to remove empty rows and columns. Hint: We will performs Unpivot and Pivot on all the columns.

Removing rows and columns from a table in Excel

Open the Excel file with the relevant table you wish to condense.

If you use Excel 2016, in Data tab, click From Table (If you use Excel 2010 or 2013, install the Power Query Add-In and find From Table in Power Query tab).

Let's extend the technique to remove rows as well

In Home tab click Remove Rows, then click Remove Blank Rows.

Removing empty rows from a table in Excel using Power Query

To repeat the same process on the empty columns, we can transpose the table.

In Transform tab, click Transpose.

Next, in Home tab click Remove Rows, then click Remove Blank Rows.

One last Transpose and we are done.

That’s it. This is probably the easiest way to remove empty rows and columns, but keep reading the other techniques, to find more approaches for the same challenge.

Removing empty columns from a table in Excel using a custom function

Open the Excel file with the relevant table we wish to condense.

If you use Excel 2016, in Data tab, click New Query, then select From Other Sources and click Blank Query (If you use Excel 2010 or 2013, install the Power Query Add-In and find Blank Query in Power Query tab, From Other Sources).

We start with a blank query to remove empty columns and rows from a table in Excel using Power Query

Rename the new query to FnRemoveEmptyColumns. Not sure how? You will find the query name in the right pane.

In the Query Editor window click Advanced Query.

We start with a blank query to remove empty columns and rows from a table in Excel using Power Query

 

In Home tab of the Query Editor, click Advanced Editor and copy the following formula.

 

Note: This formula is query function that receives a table and remove its empty columns. It uses List.Accumulate to iterate over the columns, and removes the empty ones. To learn more about List.Accumulate go here.

Update: If you are interested in a version without List.Accumulate iteration, here is another approach that uses Table.SelectColumns (Thank you, Imke Feldmann for the idea).

 

Paste any of the two formulas above to the Advanced Editor dialog, and click Done.

Now you can close the Query Editor (In Home tab, click Close & Load), and save this workbook. The two functions we have created above will help you whenever you need to remove empty columns and rows.

Back in Excel, select any cell in your table and click From Table in Data tab (In Excel 2016. For older versions with Power Query add-in installed, you will find the button From Table in Power Query tab).

Load the table to Power Query to remove empty columns and rows in Excel

In Query Editor, ensure you see the formula bar. If you don’t, check the Formula Bar checkbox in View tab.

Now click the f(x) button.

Add a new step by clicking the fx button in Power Query to remove empty columns and rows in Excel

A new step will be created with the formula (The formula calls for the output of the previous step).

Change the formula as follows, invoking the function FnRemoveEmplyColumns with the table from the previous step.

Wrap the previous step with the function FnRemoveEmptyColumns in Power Query to remove empty columns and rows in Excel

You can now see in the preview window, with the condensed table.

Click Close & Load to load the table to Excel.

Close the Query Editor to finally remove empty columns in Excel

From now on, clicking Data –> Refresh All will import the original table, remove its empty columns and load the condensed table into a new table in Excel.

Removing all empty rows and columns

Let’s take our challenge one step further, and remove both empty columns and empty rows.

Click From Table, and repeat all the steps above on a table that contains empty rows and columns.

Let's extend the technique to remove rows as well

The trick is to use the Transpose transformation, which will transpose the table so columns and rows will be switched (Any cell that is originally in row X and column Y will now be in a row Y and column X). To perform the transpose, go to Transform tab and click Transpose.

We use the transpose in Power Query to switch between rows and columns Apply the function FnRemoveEmptyColumns on the table (as shown earlier, by clicking the f(x) button and wrapping the formula). When you are done, click Transpose again, and your table are now removed of empty columns as well as rows.

Note: The double transpose will lose automatic data types on the columns. You can now convert the types of important columns with dates and numeric values. In the screenshot below, you can see how to convert the fourth column to Date.

After the last transpose in Power Query we can assign the data types on the columns

If you wish to remove empty rows only, and keep empty columns, you can skip the part where we applied FnRemoveEmptyColumns on the table, and start with a Transpose. Then apply the function and perform a second Transpose.

The coolest technique to remove rows and columns: Unpivot / Pivot

Instead of using FnRemoveEmptyColumns, here is a different approach to remove empty rows and columns. You can apply it on rows only, columns only, or on both.

Like the first approach above (where we applied the Remove Blank Rows), you can use the Query Editor’s user interface to manually perform the entire transformation steps. But unlike the first approach, it is not trivial, especially if you are not familiar with Unpivot in Power Query. To learn more about Unpivot go here.

The Unpivot transforms the select columns into a two columns where in each row you will find pairs of Attribute and Value. For each cell in the original table, the Attribute will contain its column name, and the Value will contain its value. And here is why we will use Unpivot – It ignores empty cells. As a result, pivoting the table back will lose the entire rows and columns which were originally empty.

Here are the steps:

  1. Import the table into Query Editor.
  2. In Add Column tab, click Index Column.
  3. Select the column Index, right click on its header and select Unpivot Other Columns. And here is the big trick – Power Query will remove all the empty cells following the Unpivot step.
  4. Now select the column Attribute, and in Transform tab, click Pivot Column.
  5. In the Pivot Column window, select Value as Values Column, and select Don’t Aggregate in Advanced Options –> Aggregate Value Function. Then click OK (See screenshot below).
  6. Finally remove the column Index.

The Unpivot-Pivot tick to remove empty columns and rows from a table in Excel using Power Query

And here is the query function with the steps above. To use the function, create a blank query with the formula below, and then apply it on your table (As shown in the first section above – By clicking the f(x) button and wrapping the table name with the this new function).

Conclusions:

The need to remove blank rows and columns is quite common. For those of you who are already familiar with Power Query, you will find this method even more handy.  Whenever you append multiple tables (e.g. Appending tables from a folder), you will find out that some of the tables can have unexpected empty columns or rows.

But now you can apply this technique to “trim” the tables before they get appended.

Common use case – imagine you collect Excel workbooks from different persons. All the workbooks share the same format, but once in a while, one of the data-entry guys adds an empty row or a column to space out the information in the table. You already import the files and append them together with Power Query, but those unexpected empty columns/rows can constantly break your queries during a refresh.

If you are an advanced Power Query user, I am sure you will find additional ways to perform remove empty columns and/or rows.

Please share your feedback in the comments below.

Hope you enjoyed this tutorial.

 

4 comments

  1. Maxim Zelensky Reply

    HI Gil.
    Interesting trick with List.Accumulate, never used it such way before.
    But why you didn’t used “Delete Rows” –> “Delete Empty Rows” buttons from UI? Then we can transpose and repeat.

    • Gil Raviv Reply

      You are absolutely right. I ignored the existence of this button. Using “Remove Blank Rows” is a great solution. Thank you Maxim. I’ll add this solution to this post.

  2. Anonymous Reply

    If I want to remove the whole row where is blank/null record on Inventory Item column. How can I do it?

    • Gil Raviv Post authorReply

      Are you trying to filter a column with a combination of [record] objects and bulls, and cannot find the filter control in the header?

Leave a Reply