Rename Blank in a Slicer in Power BI - Excelerator BI

Rename Blank in a Slicer in Power BI

A common problem I see some people struggle with in Power BI is when a slicer contains a (Blank) record for some reason.  The cause of the problem is not obvious and hence it is not clear how to fix it.   This article describes what causes this to occur and also how to fix it (properly, and also with a work around if you can’t fix it properly).

The Problem

There are 2 types of tables in Power BI – Data (transaction) tables and Lookup tables.  A properly designed Power BI data model will always ensure that the lookup table contains ALL of the unique items in the data table – nothing is missing.  The problem of a (Blank) in a slicer occurs when you have an entry in the data table for which there is no corresponding entry in the lookup table.  When this happens, you will end up with a (Blank) in a Slicer attached to the lookup table as well as in the Visual.

Example

Let’s say you have a Products table (Lookup table) and a Sales table (Data table) and you have sales recorded in the Sales table for a product that is not present in the Products table. In this example, when you add any column from the lookup table (eg Product Name) to a Slicer and/or a Visual in Power BI, you will see a blank value for the missing product as shown in the image below.

The Solution

You can (and should) fix the problem at the data source. This is the best solution – fix it at the source.  But in some cases it may not be viable to fix it at the source. If this is the case, you can resolve the problem using Power Query.  You simply retrieve the missing entries in the Lookup table from the Data table and append them to the Lookup table. In the example below, I have given the missing product a name “Unknown”.  After making this change, the Slicer and the Visual reflect that it is an “Unknown” product instead of returning a blank, as shown in the image below.

The Method

Rather than write step by step instructions on how to do this in Power Query, I have recorded a video and posted it to my YouTube Channel.

And, here is the link to the Power BI Report Themes blog article that I mentioned in the video.

https://exceleratorbi.com.au/changing-defaults-in-power-bi/

3 thoughts on “Rename Blank in a Slicer in Power BI”

  1. Pingback: Pre-Filtering Power BI Slicers – Curated SQL

  2. Hi Matt, if some of your viewers would want to investigate which table relationships suffer from the blank row phenomenon the following queries may help to clarify this. The queries should be run in DAX studio or any other tool that can execute a DAX query.

    // detects blank row condition – returns true/false
    EVALUATE
    ROW (
    “Blank row exists between 2 related tables”, NOT (
    ISEMPTY (
    EXCEPT (
    DISTINCT ( manytable[onetableforeignKEYcolumn] ),
    DISTINCT ( onetable[primaryKEYcolumn] )
    )
    )
    )
    )

    // returns the foreign keys from the many-table that are missing in the one-table or an empty table if none are missing.
    EVALUATE
    EXCEPT (
    DISTINCT ( manytable[onetableforeignKEYcolumn] ),
    DISTINCT ( onetable[primaryKEYcolumn] )
    )

    Example:
    ********
    If a sales table ‘Sales’ exists and the sales table is related to a product table ‘Products’ and they are related through the ‘Sales'[Productkey] and ‘Product'[Productkey] columns then …

    EVALUATE
    ROW (
    “Blank row exists between 2 related tables”,
    NOT (
    ISEMPTY (
    EXCEPT (
    DISTINCT ( ‘Sales'[Productkey] ),
    DISTINCT ( ‘Product'[Productkey] )
    )
    )
    )
    )

    … will return TRUE if a blank row exists otherwise FALSE.

    This query:

    EVALUATE
    EXCEPT (
    DISTINCT ( ‘Sales'[Productkey] ),
    DISTINCT ( ‘Product'[Productkey])
    )

    Will return the product keys that don’t exist in the ‘Products’ table or an empty table (no rows) if all product keys from the sales table are contained in the ‘product’ table.

    Hope it can help somebody to preflight the data model and make the adjustments needed (as you show in the video).

    Best regards Jes.

  3. Pingback: Power BI visuals, Q&A updates, a Cold Open and more... (December 10, 2018) | Guy in a Cube

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top