Power BI - Blank/Empty to 0

[rkrt1]

Hi Lovely Fellows

I have a PBI report and have plenty of columns with empty values. I want to replace all empty cells with 0 in all columns (not each column individually).

Anybody can pls share an approach to accomplish this?

Thanks

 

[MisterBates]

Could you adjust your source query, and in the editor, CTRL-click each column where you want to replace nulls with 0, then right-click one of them choosing "Replace Values", then fill out the dialog to replace "null" with 0?

 

[Randall]

Assuming you're working with a column that is populated based off a calculated measure, you could use the below (example from my own PBI):

TOTAL ITEMS COUNTED = IF
('CYCLE COUNT'[TOTAL ITEMS] > 0,IF(CALCULATE(COUNT('CYCLE COUNT'[GAL#]),FILTER('CYCLE COUNT','CYCLE COUNT'[COUNTS]="COUNTED")) = Blank (),
0,
CALCULATE(COUNT('CYCLE COUNT'[GAL#]),FILTER('CYCLE COUNT','CYCLE COUNT'[COUNTS]="COUNTED"))),
CALCULATE(COUNT('CYCLE COUNT'[GAL#]),FILTER('CYCLE COUNT','CYCLE COUNT'[COUNTS]="COUNTED")))

Basically what you're doing is taking your calculation and adding it to a conditional IF statement. Copy your calculation, then type "IF(" and then paste. Close it and type "= BLANK (),0," then you'll paste your calculation again, type "," and then paste your calculation one more time and close it off.

 

[rkrt1]

Assuming you're working with a column that is populated based off a calculated measure, you could use the below (example from my own PBI):

TOTAL ITEMS COUNTED = IF
('CYCLE COUNT'[TOTAL ITEMS] > 0,IF(CALCULATE(COUNT('CYCLE COUNT'[GAL#]),FILTER('CYCLE COUNT','CYCLE COUNT'[COUNTS]="COUNTED")) = Blank (),
0,
CALCULATE(COUNT('CYCLE COUNT'[GAL#]),FILTER('CYCLE COUNT','CYCLE COUNT'[COUNTS]="COUNTED"))),
CALCULATE(COUNT('CYCLE COUNT'[GAL#]),FILTER('CYCLE COUNT','CYCLE COUNT'[COUNTS]="COUNTED")))

Basically what you're doing is taking your calculation and adding it to a conditional IF statement. Copy your calculation, then type "IF(" and then paste. Close it and type "= BLANK (),0," then you'll paste your calculation again, type "," and then paste your calculation one more time and close it off.

Thanks @Randall