Change Blanks to Zero in Table

[Anonymous]

Hi there,

I have a table carrying integers which contains blanks. Ideally I would prefer these blank values to contain 0. Can I create a measure that acheives this? 

Thanks 

[Anonymous · Accepted solution]

I found a quick and easy solution here if anyone is interested: 

https://community.powerbi.com/t5/Desktop/show-items-with-no-data-as-0-instead-of-blanks/td-p/113160

See Seans's solution specifically. It's very similar to one of amitchandak's reccomendations. 

Kind regards

[v-kelly-msft]

Hi  @Anonymous ,

If you wanna replace all the blanks in a table into 0,you'd better do it in query editor using below M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTJUitWJVgIyjMAMYyALJmIMZpgAWaZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type"))
in
    #"Replaced Value1"

And you will see:

Using measure can only replace the values in a certain column,see below:

Measure = IF(ISBLANK(MAX('Table (2)'[Column1])),0,MAX('Table (2)'[Column1]))

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hey Kelly, appreciate the help.

I tried using your M Code solution but it makes all my visuals go blank.

In regards to 

Measure = IF(ISBLANK(MAX('Table (2)'[Column1])),0,MAX('Table (2)'[Column1]))

Will that measure work if the data in my visual is also a measure?  so instead of 'Table (2)'[Columnx] its

'Table (2)'[Measurex]

Thanks

[Anonymous · Accepted solution]

I found a quick and easy solution here if anyone is interested: 

https://community.powerbi.com/t5/Desktop/show-items-with-no-data-as-0-instead-of-blanks/td-p/113160

See Seans's solution specifically. It's very similar to one of amitchandak's reccomendations. 

Kind regards

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Yes,it is also supported if the value is a measure,see below:

If your issue is solved,could you pls mark the reply as answered to let more people find the solution?

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , Add +0 , but will create a complete left join 

New measure = [measure] +0

Another example of controlled +0

0 between range //only between range

Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))

[Anonymous]

HI there,

I dont know how to embed this solution into my power bi report.