cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate I
Advocate I

Replacing (Blanks) with 0

Hi,

 

I have a number of Cards in my dashboard that return numbers. When I selected a filter where there no numbers, instead of displaying 0, it displays (Blank), and looks very ugly. Is there an easy way of getting these to simply display the number 0 or "" instead of (Blank)?

 

Blanks.png

1 ACCEPTED SOLUTION

@Espen1515,

so you are using the raw source data, is it correct?

 

So its easy to you just make new column with this code

 

Column = IF(ISBLANK(**original column**),0,**original column**)

**original column** - your name for the column you are using.

Then change the value to the new column and it should work 🙂

View solution in original post

11 REPLIES 11
Regular Visitor

If you want is to replace blank or null values with 0 without creating a new column, then use the Query Editor.

  1. Select Edit Query. 
  2. In the query window, right click on the column header and select Replace Values... 

Notes:

  • Pay attention to where you insert the command in your Query steps. 
  • You can select multiple columns by holding down the Ctrl key when you select the column header
  • You can view and modify the code in the Advanced Editor if needed.
  • You can replace any values with this method
Regular Visitor

There is a simpler solution, 

Click on "Edit Queries" ribbon in the toolbar
Select the "Database"(Incase if you have multiple database imported into PBi )
Select the respective "Column" in that databse which has blanks
Choose the option "Replace Value"
Value to find: null
Replace with: 0
Click ok

 

Taaadaaa!! 

Helper II
Helper II

Hello @Espen1515,

There is quick fix. Lets say its measure [XAZ]. The Easiest way to do it is to make new measure

XAZ1 = IF(ISBLANK([XAZ]),0,[XAZ])

Same if its based on column, just do new column insted of measure 🙂 Or implement the condition inside original measure.

Solution Sage
Solution Sage

Adding +0 to the end of your measure is one quick way to fix this, or adding a new measure that looks at the existing one, and does a simple "if blank then 0 else existing measure" replacement

Hi, I am simply using a card where I COUNT and filter on a particular field. I don't think I am using a measure. When you say measure here, do you mean that I should create a new column that copies the column, but using the if function instead?

@Espen1515,

so you are using the raw source data, is it correct?

 

So its easy to you just make new column with this code

 

Column = IF(ISBLANK(**original column**),0,**original column**)

**original column** - your name for the column you are using.

Then change the value to the new column and it should work 🙂

View solution in original post

Hi,

 

Yes, that is correct. I will try that. I was just hoping that I would not have to create a whole new column and instead just select a format or something like that on my Card. Something like "display blanks as "-" or display blanks as 0, etc. Power BI should have a function like that in order to minimize the amount of tampering with source data. 🙂

Hi,

 

You dont have to worry about "amount of tampering". When you are creating custom columns it doesnt take any memory. Its one of the main benefits using DAX instead of Excel functions 🙂

Hi,

 

I tried using your function now, but as this is a date field, it is returning numerical values for all dates where it is not blank. If I then convert those to date format, my 0's show as 30th December 1899 instead.

Hi @Espen1515,

thats changing the situation a bit.

You can try work-arounds.

 

1st of all its needed to know, that powerBi supports only one format inside one column. Which means that 0 in DateFormat is always shown as the date you wrote.

Only blank value ("null") is in neutral format, which shows "blank".

 

So if you want to show Date or 0, you would have to make a column in text format. Which will be limited in some functions.

Or you can try establish a visual / page filter where your original column will be set to all non blanks and it will be the value of data cards.

Hi,

 

Thanks. Ya I thought it was something like that. Anyhow, I think Power BI ought to have a functionality (similar to conditional formatting), where you can select any graph, table, card, KPI, etc., and say if value = blank, then X, etc.

 

Anyhow, I think I have the best solution possible. So thanks for your help! 🙂

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors