Remove Blank Rows and Columns from Tables in Power Query

When you import data to Power Query, it's not unusual to end up with a table containing blank rows and columns, like this

There are a few ways to get rid of these 'blank' rows and columns, some better than others.

Watch the Video

Unpivot and Pivot

The first method involves unpivoting and then pivoting the data. This works because when you unpivot, Power Query automatically removes any null values.

Start by adding an Index column

Select the Index column, right click the column header and then click Unpivot Other Columns

Next, select the Attribute column and from the Ribbon, Pivot that column

In the Pivot column options, choose the Value column as the values. In the Advanced Options, choose not to aggregate

Giving this table as the result (I've deleted the Index column).

Not all the 'blank' rows and columns have been removed, I'll get to that shortly. But first I want to point out that by using this method, you lose any data type changes that were made before the unpivot/pivot.

Before Unpivot/Pivot

Why Aren't All the 'Blank' Rows and Columns Removed?

Because they contain something other than null. Unpivot/Pivot will remove any nulls, but if you have things like spaces, empty strings or non-printing characters like tab or carriage return, these all get left unchanged.

Looking closely at the source data table, you can see that the first 3 rows on the Init column contain spaces (trust me), and rows 4, 5, 6 and 7 contain non-printing characters. CHAR(9) is tab and CHAR(13) is carriage return.

Another approach is needed to deal with these types of characters.

Writing M Code

OK, so the better approach requires writing some M code. Sorry, you can't get away from this. But if you don't want to write the required code (or even learn how it works) then you can just download the example Excel workbook I created and use the function I wrote. No need to know how it works, just use it.

Removing Blank Rows

To start, I'm going to use the Ribbon in Power Query to Remove Blank Rows (removing columns comes later).

This results in a new step with this code (I've formatted it so it's easier to read)

This code needs some additions to deal with spaces and non-printing characters.

The following code is the result of these additions. I've color coded it and formatted it to try to make it easier to understand my explanation of what it does. You can also watch the video for my explanation.

1. Record.FieldValues passes the values in each row to List.RemoveMatchingItems

2. List.RemoveMatchingItems removes empty strings "" and null values

3. List.Transform uses Text.Clean and Text.Trim to remove spaces and non-printing characters from the items in the list produced by Step 2. Text.Clean and Text.Trim replace the values they remove with an empty string.

4. List.RemoveMatchingItems removes empty strings "" produced by Step 3

5 and 6. Select rows from the table that are not empty

Removing Blank Columns

This code is hand written and is shown in the image below.

1 and 2. Table.Column goes through each column (selected by Table.ColumnNames) and passes the values in each column to List.Transform

3. List.Transform uses Text.Clean and Text.Trim to remove spaces and non-printing characters from the items in the list (each column). If Text.Clean and Text.Trim result in an empty string, replace that with null.

4. List.NonNullCount counts the number of non-null items in the list (the column)

5. List.Select uses the results from List.NonNullCount to create a list of the column names that contain data

6. Table.SelectColumns uses this list to select only those columns into a new table

The result is this

Creating a Function From This Code

Whilst writing all of that would be fun every time you needed it, it'd be easier to use a function to clean up your tables.

To create a function from this code, first of all duplicate the query, and call it something useful like fxRemoveBlanks.

Open the function query in the Advanced Editor and you'll see this.

The changes needed to make this code a function are:

1. Add the function declaration and pass in a single parameter called Source which will contain the table

2. Delete the first 2 lines of code

3. Change the table being worked on in the #"Removed Blank Rows" step to Source

Resulting in this

Using the Function

If you have a query like this where the Source is loaded in the first step

Just call the function, passing in the code that loads the table as the function's parameter.

Or, at any point in your code, you can call the function as long as you pass in a table as the parameter.