This blog will demonstrate how to automatically remove all empty columns in Power BI through the Power Query editor. The approach will involve the standard Table.Profile M library function and its additional aggregates parameter to add a non-empty count column. You can watch the full video of this tutorial at the bottom of this blog.
Sample Query
The first step is to launch the Power Query editor in Power BI. But if you’ve never seen a Table.Profile because you can’t invoke it through the user interface, there won’t be a button for you to push at this time.
So I’ve referenced my sample query and duplicated the initial step to wrap the function Table.Profile around it. Inside the formula bar, I will type tp, find and select Table.Profile, and click the checkmark (?) beside it.
And this tells me something about the data in my table below, which even has a NullCount column.
Data Set to Remove Columns in Power BI
If you only want to remove empty columns in Power BI with nulls, you can click on the Filter option with the NullColumn, select 11 and press that check mark (?). Click OK to complete the changes.
As you can see below, this action results in an error which is a bug. However, I found that projection is a simple way around this bug.
Select Columns
So we’ll select the previous step, TableProfile, on the APPLIED STEPS on the right-hand side and choose only the columns that we need for the logic we are trying to build.
So in a set of square brackets [ ] for projection, let’s select the columns we want to retain from this table.
And in another set of square brackets, we’ll select the column Column, enter a comma, and set the Count and Null columns.
Finally, let’s press that check mark and go back to the original step, FilteredRows, in the APPLIED STEPS to resolve the error as you can see below.
Now, if you’re only interested in whatever remains in the column Column as it has the column names, do this next action. In a set of single square brackets, select Column and press the check mark afterward.
This action returns a list with the column names. You can use this list in the function called Table.RemoveColumns by first pressing fx in front of the formula bar.
We’ll replace #“Filtered Rows” with Sample to return to the initial step in our query and press the check mark. This action returns all of our previous rows, as you can see below.
Now we can wrap that function around it. So let’s select Table.RemoveColumns and follow it with Sample and #“Filtered Rows” in open and closed parenthesis. Finally, we press the check mark.
Add Non-Empty Count Column
Now that we know this technique works, we can look into that optional aggregates parameter to add a non-empty count column to the Table.Profile function. So we’ll duplicate this query by right-clicking it and hitting Duplicate.
Next, we’ll move it back to the TableProfile step.
Let’s also remove that projection so we can return the output of the Table.Profile function itself.
And inside the formula bar, we enter a comma after Sample to get to its second parameter, the additional aggregates in the list containing list. We can add a new column name as text and, in this case, let’s call it “NonEmpty” and follow it with a comma.
Then there’s a type check function to select the rows from the table. We can enter each Type.Is, followed by opening parentheses and underscore.
We continue by putting Any.Type to access each item from our column as we want to consider all of the data types in each of the columns.
And instead of each Type.Is, you can also use each true and get the same results. To do that, we enter each true followed by a comma.
Next, you’ll need to enter an aggregation function by typing each followed by List.Tranform. This action cleans the text and trims extra spaces of our single-column list. We continue by entering (_, each text.Trim( Text.Clean (_).
And since we want to consider all the column types, this means that if a column type is incompatible with text, this will return an error. To deal with that, add each try and otherwise.
We want the value that’s currently there at that position in the list. For that reason, List.Transform will then return a list.
Additionally, we want to select each item that does not return an empty Text.Trim and emit them. Thus, we’ll wrap it with List.Select and add each.
Finally we have to count the NoNull values, so all the way at the front we’ll add a List.NoNullCount, and press that check mark (?) in front.
We need to emit each row returning a 0 in the non-empty column. Let’s update the remaining steps by projecting the column and the non-empty column.
In a set of single square brackets, put Column, followed by a comma, and lastly NonEmpty all the way to the end. Press the check mark to complete the process.
We can now select the Filtered Rows in the APPLIED STEPS, but doing so will result in an error.
Thus, we will replace [NullCount] = 11 with [NonEmpty} = 0 and press that check mark. This action returns a list with two column names that we can pass to the Table.RemoveColumns function.
Custom Function to Remove Empty Columns in Power BI
I’ve also added a custom function above for your reference to remove the empty columns and rows in one go. As you can see below, it has that same additional column it adds to the Table.Profile function and then uses that to filter out those columns.
***** Related Links *****
How To Use Power Query Row And Column Selection
Power Query: How To Merge Tables W/Different Columns
Power BI Unpivot Columns – Power Query Tutorial
Conclusion
Having to remove blank rows and columns is quite common. If you are already familiar with Power Query, you will find the function above handy.
When you append multiple tables, it can result in unexpected empty columns or rows in some tables. But now, you can use this technique to trim the tables from blank rows and columns before appending them.
All the best,
Melissa de Korte
[youtube https://www.youtube.com/watch?v=l_7XLCt35y4&t=4s&w=784&h=441]
