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.

A table in Excel. Columns A, D, F, H and J are blank and highlighted. Rows 8, 12 and others are blank and highlighted as well. In the remaning cells in the table there is text values, numbers and dates. The challenge is to remove all blank rows and columns in 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?

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

A table in Excel. Columns A, D, F, H and J are blank and highlighted. Rows 8, 12 and others are blank and highlighted as well. In the remaning cells in the table there is text values, numbers and dates. The challenge is to remove all blank rows and columns in Power Query. In the Data ribbon, From Table is highlighted.

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

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

= #"Changed Type"

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

= FnRemoveEmptyColumns(#"Changed Type")
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 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.

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

21 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?

  3. Credalytics (@Credalytics) Reply

    Thanks for the great article, there really was a wealth of info here.

    I tried the first method to remove empty columns however after the final iteration transposing the table back to vertical columns and I loaded back to excel, everything worked well except I no longer had any headers.

    These headers are essential as I then need to work with Pivot Charts after the data has been cleaned. I’m wondering if there is a way to preserve the headers?

    I managed to then get the first script working though I must admit I hadn’t a clue what i was doing. I’m not sure what the following code was about: = #”Changed Type“.

    I ignored the syntax from the code and it seems to have work.

    Many thanks !!

    • Gil Raviv Post authorReply

      Thank you for the feedback. When you transpose, you can preserve headers by demoting the headers as first row before the transpose. You have this option in “Use First Row As Headers” drop down menu in the ribbon.

  4. Credalytics (@Credalytics) Reply

    Thanks for the reply Gil, I really like this approach however when demoting the headers this present an impasse as the empty columns (of which there are many throughout the data range) all have headers. Once I transpose, the remove column function ignores the empty columns as the headers are still present. Catch 22!!

    • Giovany Rodriguez Reply

      That’s what happened to me, that technique has its limits. You have to use the last approach, with the index column and unpivot/pivot, with this way you don’t have any troubles.

  5. Anonymous Reply

    I want to delete only columns which are empty (null values). I have tried your each method, but unable to work out. Please help.

    • Gil Raviv Post authorReply

      Can you share the M code and the error you received?

  6. Anonymous Reply

    Thanks for this article, I need to delete unused/disable data-table and columns for Power BI performance improve so like this query is there anyway or query to exclude that.

    Thanks in advance

    • Gil Raviv Post authorReply

      Hi Eddy,
      Thank you for sharing. Performance-wise, I am not sure if Pivoter solution is perfoming as well as Imke’s suggested solution above.

  7. Aleksey Reply

    Hi. Thanks for article. There’s another way to remove all columns:
    (tbl) =>
    Table.FromColumns(
    List.RemoveNulls(
    List.Transform(
    Table.ToColumns( tbl ),
    each if List.RemoveNulls(_)={} then null else _
    )
    )
    )

  8. Salem Reply

    Hi Gil

    I used the Imke’s suggested solution and it work perfectly
    Can we improve this solution if we have a big table and we know in advance which column header to inspect for null data
    Example, empty column occur only on column headers “Shopxxx”
    So the query fetch for empty data only on columns with text “Shop”

  9. Anonymous Reply

    Thank you for your extensive work. I can’t get this to work as my entire columns contain 0 on each row instead of “null” / blank.

    Tried to change your codes from:

    each _ null

    each _ is null

    to:

    each _ = 0

    But the table doesn’t load – been spinning for an hour. Any advice you could give me?

  10. Jerry Norbury Reply

    To retain the column order you’d grab the column order prior to empty removal, remove the empty using unpivot/pivot, grab the new column order (excluding the now deleted columns) and then reorder column based on the List.Intersect ({ before, after })

    let
    Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
    before = Table.ColumnNames(Source),
    #”Added Index” = Table.AddIndexColumn(Source, “Index”, 0, 1, Int64.Type),
    #”Unpivoted Other Columns” = Table.UnpivotOtherColumns(#”Added Index”, {“Index”}, “Attribute”, “Value”),
    #”Pivoted Column” = Table.Pivot(#”Unpivoted Other Columns”, List.Distinct(#”Unpivoted Other Columns”[Attribute]), “Attribute”, “Value”),
    #”Removed Columns” = Table.RemoveColumns(#”Pivoted Column”,{“Index”}),
    after = Table.ColumnNames(#”Removed Columns”),
    OldOrder = List.Intersect({before,after}),
    #”Reordered Columns” = Table.ReorderColumns(#”Removed Columns”,OldOrder)
    in
    #”Reordered Columns”

  11. Anonymous Reply

    Great additions to the post, exactly what i was hoping for, much appreciated!

Leave a Reply