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 that are entirely empty, and usually, you don’t have prior knowledge of which columns and rows are empty.

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?

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 perform 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 the 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).

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

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).

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.

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

(tbl) =>
let
    Headers = Table.ColumnNames(tbl),

    fnMyAccumulator =
        (tbl as table, columnName as text) as table =>
            if List.MatchesAll(Table.Column(tbl, columnName), each _ is null) then Table.RemoveColumns(tbl, {columnName}) else tbl,
    
    myAccumulatedResult = List.Accumulate(
        Headers,
        tbl,
        (tbl, columnName) => fnMyAccumulator(tbl, columnName))
in
    myAccumulatedResult

Note: This formula is a query function that receives a table and removes 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).

(tbl) =>
let
    Headers = Table.ColumnNames(tbl),

    Result = Table.SelectColumns(
                 tbl,
                 List.Select(Headers, each List.MatchesAny(Table.Column(tbl, _), each _ <> null)))
in
    Result

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).

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.

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

= #"Changed Type"

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

= FnRemoveEmptyColumns(#"Changed Type")

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

Click Close & Load to load the table to 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.

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.

 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 is 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.

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 two columns wherein 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.

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 this new function).

(tbl) =>
let
    #"Added Index" = Table.AddIndexColumn(tbl, "Index", 0, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] <> null and [Value] <> ""),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

Conclusions:

The need to remove blank rows and columns is quite common. 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 one of the techniques that are described in this article to dynamically remove empty rows and columns.

Here is a 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. You can download the solution file here.