Automatically remove empty columns and rows from a table in Power BI

Hi again. This is Maya (you can find me on Linkedin here), with my second post on DataChant: a revision of a previous tutorial. Removing empty rows or columns from tables is a very common challenge of data-cleaning. The tutorial in mention, which happens to be one of our most popular tutorials on DataChant, addressed how to remove empty columns and rows using Excel (you can find the tutorial here). But if you’d prefer removing empty rows and columns using Power BI Desktop, you can follow this tutorial. I have also updated the screenshots and data (with some very important pop culture references).

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. The data gets frequent updates. You need to import the table and condense 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 in Power BI Desktop.

Ready to start?

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

Open the Power BI report that contains a table with empty rows and columns. In the Home tab, click on Transform data.

In Power Query Editor, select the query of the table with the blank rows and columns. 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, because we don’t have Remove Blank Columns in the UI.

In Transform tab, click Transpose.

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

One last Transpose and we are done.

In the Home tab, click Close & Apply to load your table onto Power BI.

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 using a custom function

Open the Power BI report that contains a table with empty columns. In the Home tab, click on Transform data.

Now that you are in Power Query Editor, in the Home tab, click New Source, then select Blank Query.

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

In Home tab of the Power Query Editor, click Advanced Editor.

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

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 box, and click Done. Now, in the Queries pane on the left, select the query of the table with empty rows and columns. Ensure you see the formula bar. If you don’t, check the Formula Bar checkbox in the View tab.

Now click the f(x) button.

A new step will be created with a reference to 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.

In the Home tab, click Close & Apply.

Removing all empty rows and columns

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

Open the Power BI report that contains a table with empty columns and rows. In the Home tab, click on Transform data. Repeat the steps from the previous technique starting from the creation of the FnRemoveEmptyColumns function (unless you are working on the same file, in which case the query is already created).

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 the automatic data types conversion you had 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.

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 Power 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 table into two columns. 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:

Open the Power BI report that contains a table with empty columns and rows. In Add Column tab, click Index Column. 

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.

Now select the column Attribute, and in Transform tab, click Pivot Column.

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

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. For those of you who are already familiar with Power Query, you will find the function above 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 from blank rows and columns 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 from a folder 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 techniques to perform remove empty columns and/or rows.

Please share your feedback in the comments below.

If you found this tutorial helpful, you will find plenty more useful techniques in my book: [amazon_link asins=’B07HP9J35M’ template=’ProductAd’ store=’datachant-20′ marketplace=’US’ link_id=’50158a70-da39-11e8-bc35-73ed4db2ccf0′]