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
Download Example Excel Workbook
Enter your email address below to download the sample workbook.
This Works In Power BI and in Excel
The M code shown here can be used in Power Query in both Power BI and Excel.
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.
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.
Hi Philip, I finally had cause to use this fabulous technique and I came across a limitation to it being that the code will fail if there are any errors (like #N/A) in the data being imported. Now we can remove these prior to using your technique, but since your code parses the whole data, I was wondering if your code could be expanded to handle / ignore / treat-as-blank any error values it finds, or should I remove all errors first with something like this:
#”Replaced Errors” = Table.ReplaceErrorValues( Source, Table.ToColumns( Table.Transpose( Table.AddColumn( Table.FromList( Table.ColumnNames(Source), Splitter.SplitByNothing(), null, null, ExtraValues.Ignore), “Custom”, each null))))
Modifying my code would involve adding something like you’ve already written, so you may as well just add your own code to remove the errors.
Is it possible to have this example in Power BI format with the functions to follow along the video?
Power Query in Power BI is the same as Power Query in Excel, so you should be able to follow along, but if you get stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further.
These posts are great and useful Thanks to the treacy family
You’re welcome Farshad, glad it was helpful.
Phil, This is awesome. I have needed this for some time. I read through this a few times & follow the code, but I doubt that I would ever have figured this out on my own. Because of that, I’ve copied both the Code & function into my ExcelTips workbook for future use. Thanks for doing all the hard work to develop this.
No worries Jim, glad you found it useful.
The function you created is really useful and the video expalantion was great. If I want to use the function regularly in other queries what is the best way to do this? Is it just a case of copying the code from the advanced editor and creating a new query in each instance or is there a better way to share functions between files?
The quickest way I find to copy queries is to click the query name in the original file, CTRL+C to copy it, then in the new file, CTRL+V and the entire query will paste in.
The function given is so easy to use.
What I do is deleting manually blank columns and then transpose and changing data type and again manually deleting blank columns and again transpose.