Learn how to add blank rows in Power BI or remove them from visuals to make reports easier to read. Combine blank rows with showing values as rows in a matrix visual to make it even easier to sort and arrange rows in a specific order.
In the screenshot below we’ve used two different techniques to format our measures in a way that you would likely see on a Power BI financial statements.
Each row is a separate measure, which has many benefits including making it easier to perform calculations like gross profit.
Then a blank row is added for visual separation between rows.
To add blank rows to a Matrix Visual in Power BI, create a new empty measure. Rename the measure with a space to give the look that it is blank. Blank Rows can be removed by filtering at the visual or page level. Blank rows can also be removed by filtering a data model in Power Query.
Table of Contents
Let’s take a look in more detail!
How to add Blank Rows to a Matrix in Power BI
To create a blank row in a matrix visual, create an empty measure to add as a column or row. Then rename the new empty measure to a space. The measure will still exist with a name in the Data panel with the field name that’s been assigned. Changing the name to a space will only impact the visual.
The blank row has been renamed with a space.
Double click on a measure or column name to highlight it, then Power BI lets you enter any character you want as the new name within a matrix visual. By changing the name to a Space it will appear as if it is blank.
How to Create a Null, Blank, or Empty Measure in Power BI
To create a blank measure in Power BI, click on New Measure from the Power BI ribbon.
Give the measure a name, it can be anything. This is the name that gets replaced with a space, so it is only for reference. In our example, we use the Measure Name “Blank”
Use the formula: Blank = “”
Quotes are normally used to define text in a Power BI DAX measure. It would more commonly look like:
Category = “Cookies”
Which would create a column with the word Cookies in it.
By using Two Quotes and nothing in between it will create a blank measure.
The third problem that we need to solve is that our columns have to be measures for us to be able to insert a blank measure in between them.
Switch Values to Rows in a Power BI Matrix
When using a Matrix visual, it will normally create a table that shows summarized values, much in the same way that an Excel pivot table would. In this scenario, you are unable to insert values into the middle of a column of values.
You would either have to build the blank values into your dataset, and sort it with a separate column. Or you can switch values to show on rows in a Matrix visual.
Add all of your the fields you want to represent into the Values section of the Matrix visual.
As a second step, go to Format Your visual > Values > Options
Select Switch Values to Rows
This option places all of the measures or fields into the rows of your matrix visual. This makes it much easier to work with because you can drag measure names to change the order in which rows appear in the visual.
After we add in a Blank row, we can rename it to make it look like the row is blank.
Another benefit of using this method is that it becomes easier to create subtotal measures when you can reference columns. For example, we used the measure: Gross Profit = [Total Sales]-[Cost of Goods Sold] to add multiple previous measures together. Then it’s as easy as drag and drop to get it into the order that you want.
If you do not want to have measures represented as rows of a matrix visual, there are several other advanced methods to force the sort order of rows in a Power BI matrix.
The following video is focused on financial statements, but it goes into great depth of several alternative methods to force row order in a matrix, including sorting by another column and using a switch to combine the elements together.
We have also put together a Guide to Creating Financial Statements in Power BI that covers many similar techniques and items that are good to know for formatting best practices.
Removing Blank Rows from a Power BI Matrix
To remove blank rows from a matrix visual in Power BI, select the visual. Expand the Filters panel on the right side of the screen. Expand the filter related to the field that is showing a blank row. Use basic filtering to select all except for the blank value.
An example of a matrix with a blank row
After the filter is applied, the blank will no longer show up on the visual.
Power BI provides a number of filtering settings. You can filter out a value for an individual visual, or you can drag and drop the field to the bottom of the Filters panel.
There is a section for Filters on this page, which applies a filter to all visuals on a page. Filters on all pages will hide a blank row from all visuals in the enter report across all tabs.
Remove Null or Blank Rows from a Dataset in Power Query
You can filter out a null or blank in Power Query. It is a more robust solution than filtering visuals. By filtering the data model, you ensure that the null or blank values will never be visible. It will also prevent possible errors when trying to aggregate columns with empty values.
To remove blanks or nulls from a data set in Power Query, First launch the Power Query Editor.
Right click the name of a column in the data panel, and select Edit Query
Click the triangle at the top of one of the columns with a blank or null value. This expands a list of all of the values available. You may have to click on Load More if there are many values and an option for (blank) or null is not present.
Uncheck the value that you want to filter out of your report.
By applying a filter in Power Query, it will no longer be available or seen in your report or dashboard.
If you need to re-select it, you will need to go back into Power Query and modify the filtering step that was added.
To add a blank row or column in Power BI, create an empty measure. Click on New Measure and use the DAX formula: blank = “”. Quotes normally surround text, but when put together it creates a blank.
Try switching a matrix to show values on rows to make it easier to drag and drop them to place them in order.
Blanks can be filtered out at the dataset level using Power Query, or you can update the filters for an individual matrix visual, a page, or for the entire report. Power Query is a recommended method to avoid future calculation errors and other issues that can arise when trying to aggregate numbers with nulls present.