Labels for Blank Values

[irishakir]

Hello!

I have blank values and zero values. They have to be visualised. I use "show items with no data" to show the blank values, but I also want user to know, that there is no data, so, I need to add a label or something on a place where blank value is. Is there any way to do this? 

[Anonymous]

If it's text data you're working with, it's possible to use the Query Editor to replace blank data with something more descriptive.

From the Query Editor:

1. Select the column where you want to replace the blank data
2. From the Transform ribbon, click on "Replace Value"
3. In the dialogue, depending on how your blanks currenly appear, you'll want to use "" or null for "Value To Find", and for "Replace With", you can use Blank or whatever you decide
4. Hit OK, and Save and "Close & Apply" the Query Editor

You might need to play around with the right way to identify your blank values in step 3. Some datasets show a single space for what looks like a blank.

 If this doesn't quite answer your question, try being a bit more descriptive about the situation your working with. Example data always helps. Show us a sample (or a mock-up) of the data you're starting with, and what you want it to look like in the end.

[irishakir]

In my situation: the less value the better. On November the second item is better because it has a zero value, but on October there is no information about the second item while looking on visualisation user may think that the second item is better on October too. So, I want to make a difference between the blank and zero values. Maybe, something like this:

[v-sihou-msft]

@irishakir

To Power BI (internally Tabular model), there's no difference between blank values and zero values when rendering data in a chart visual. No matter how you transform the value, it will not show any bar/column on either blank or zero values. For your requirement, it can't be achieved.

Regards

[emarc1]

If you conditionally format a table, you can get it to format blank cells differently. I'll often filter the blanks so the cells have a grey background.

You could also make a calculated column that has a text string warning that there's no data that could be displayed alongside.

IF( 
    Table[Column] = BLANK(),
    "No data",
    BLANK()
)

Not sure how I'd incorporate it into a bar chart, short of a custom visual.

[Anonymous · Accepted solution]

Thanks for the additional details.

The easiest way to turn blanks into zeros is to use a measure for your Values field that has a "+0" in it. Like this:

Quantity = SUM(Dataset[Value]) + 0

If you want to make it more obvious that there's a zero value in your chart, turn on data labels.

The one kink you might run into is that if, for example, there are no lines of data for series 2 in October, there will be a blank space instead of a zero for that month. I don't think there's any way around that.

[irishakir]

Thank you!