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/

Share?

Leave a Reply

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x