How to Delete Null Rows in Power BI

Blank rows in your data can make your Power BI reports look messy and can even break your calculations. Getting rid of these "null" values is a fundamental data cleaning step every analyst needs to master. This tutorial will walk you through several effective methods for deleting null or blank rows directly within Power BI's Power Query Editor.

Why You Should Remove Null Rows

Leaving null or blank rows in your dataset isn't just about appearances, it significantly impacts the quality and reliability of your analysis. Here’s why it's so important to clean them up:

• Inaccurate Calculations: When you calculate averages, sums, or counts, null values can either be ignored or treated as zero, depending on the function. This can skew your results. For example, averaging sales across ten regions, where two have null values instead of zero, will produce a misleadingly high average because the calculation will be based on only eight regions.
• Messy Visualizations: Charts and graphs can display blanks as gaps or "zero" points, cluttering your visuals and making them harder for your audience to interpret. A line chart with nulls, for example, might have strange breaks in the line, suggesting missing periods when the data is just blank.
• Relationship Problems: In data modeling, nulls can cause issues when you try to create relationships between tables. A row with a null key cannot be matched to a corresponding key in another table, effectively breaking the relationship for that piece of data and leading to incomplete data models.

By taking a few minutes to remove these rows during your data preparation phase, you ensure that your dashboards and reports are built on a solid, reliable foundation.

Accessing the Power Query Editor

Before we dive into the methods, you need to know where the data cleaning magic happens. All of our data transformation tasks will take place in the Power Query Editor. It's the built-in tool in Power BI designed for exactly this kind of work - shaping, cleaning, and preparing your data before it gets loaded into your report.

To open it, click on 'Transform data' from the Home tab on the main Power BI Desktop ribbon. This will launch a new window with a spreadsheet-like view of your data, along with a powerful set of tools for cleaning it up.

Method 1: The Simple Filter Method

The quickest and most common way to remove nulls is by using the built-in column filters. This method is perfect when you have one or more specific columns that determine whether a row should be kept or deleted.

Let's say you have a sales table, and any row where the OrderID is blank is considered invalid and needs to be removed.

Step-by-Step Instructions:

1. With your data open in the Power Query Editor, locate the column that contains the null values (in our example, OrderID).
2. Click the filter arrow (the downward-pointing triangle) on the right side of the column header. This will open a dropdown menu with a list of all unique values in that column.
3. Scroll through the list and you'll see options for (null) and/or (blank). Uncheck the box next to (null).
4. Click OK. Power Query will immediately filter out all rows where the OrderID was null.

You can also access a dedicated command for this. Instead of unchecking the box, click the filter arrow and select 'Remove Empty' from the list of options. This command combines the action of removing both null and blank values in one click, making it slightly more efficient.

You’ll notice that Power BI records this action in the "Applied Steps" pane on the right. This means the filter will be automatically reapplied every time your data refreshes, so you only have to do it once.

Method 2: Using the 'Remove Blank Rows' Command

What if you want to remove rows where every single column is blank? This often happens when importing data from Excel spreadsheets or CSVs that have empty formatting rows. Filtering column by column would be tedious and inefficient for this scenario.

Fortunately, Power Query has a dedicated tool for this exact problem.

Step-by-Step Instructions:

1. In the Power Query Editor, navigate to the Home tab on the ribbon.
2. Find the 'Reduce Rows' group and click on the 'Remove Rows' dropdown menu.
3. From the dropdown, select 'Remove Blank Rows'.

Power Query will automatically scan your entire table and delete any row where all of the cells are null or empty. This is a powerful, one-click solution for cleaning up structural blanks in your raw data files.

Method 3: Handling More Complex Null Scenarios with a Custom Column

Sometimes your business logic is more complex. You might only want to remove a row if several specific columns are all null at the same time. For instance, in a customer datalog, a row is useless only if Email, PhoneNumber, and CustomerID are all blank.

While you could apply a filter to each of those three columns one by one, a more robust way to handle this logic is with a conditional custom column. This gives you fine-grained control and a single, clear step in your query process.

Step-by-Step Instructions:

1. In the Power Query Editor, go to the Add Column tab.
2. Click on 'Custom Column'. This will open a dialog box where you can write a formula using the M language (Power Query's formula language).
3. In the dialog box, name your new column something descriptive, like IsValidRow.
4. In the 'Custom column formula' box, enter a logical statement that checks your criteria. For our example, the formula would be:

if [Email] = null and [PhoneNumber] = null and [CustomerID] = null 
then "Remove" 
else "Keep"

1. Click OK. Power Query will add a new column to your table that says "Remove" for rows that meet your null criteria and "Keep" for all others.
2. Now, use the simple filter method from Step 1 on your new IsValidRow column. Click the filter arrow, and uncheck 'Remove' so that only the "Keep" rows remain.
3. Finally, to clean up your data model, right-click the header of the IsValidRow column and select 'Remove'.

You're left with a clean table that perfectly follows your specific business rules, and the entire logical process is contained within one primary step for easier troubleshooting later.

Method 4: Filtering Blanks from Visuals with DAX (Without Deleting Data)

Sometimes you need to keep the null values in your underlying dataset for other calculations, but you just don’t want to see them in a specific chart or table. For example, you want a table of products and their sales, but you want to hide any products that have no sales (i.e., null or blank sales).

Deleting the rows in Power Query isn't the right move here, because you might want to show those zero-sale products in another report. Instead, you can apply a filter directly to the visual itself using the Filters pane in the main Power BI report view.

Step-by-Step Instructions:

1. First, make sure you have a measure created for your calculation. For example: Total Sales = SUM('Sales'[SaleAmount]).
2. In the Report View, create your visual (e.g., a table with Product Name and Total Sales).
3. Select the visual by clicking on it. The Filters pane should appear on the right side of the screen.
4. Drag your measure (Total Sales in this case) into the 'Filters on this visual' well.
5. Expand the new filter card. Under 'Show items when the value:', select 'is not blank' from the dropdown menu.
6. Click 'Apply filter'. The visual will update immediately, hiding all the products that had no sales.

This approach gives you the flexibility to control how nulls are displayed on a case-by-case basis, leaving your underlying data model intact for comprehensive analysis.

Final Thoughts

Removing null rows is a critical step in building accurate and professional-looking Power BI reports. By using the Power Query Editor to filter columns, remove blank rows, or even build conditional logic, you create a clean and reliable dataset that you can trust for all your calculations and visualizations.

For many of us, these manual data cleaning steps in tools like Power BI are one of the most time-consuming parts of the job. Spending hours in Power Query, building pivot tables, and cleaning CSVs just to answer simple business questions can get tedious. At Graphed , we connect directly to your data sources like Google Analytics, Shopify, and Salesforce and automate away that friction. You can describe the report you need in plain English - like "show me last month's sales by campaign, excluding returned orders" - and we generate a live, interactive dashboard for you in seconds, saving you valuable time to focus on the insights, not just the prep work.