Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
chris1234512
Frequent Visitor

Blanks vs Zero's when utilizing an Excel Doc as a Datasource

I have an excel doc as a data source for a power bi model. The doc utilizes multiple division formulas so in some circumstances I'm receiving a #DIV/O! error. I generally fix this error on the excel side, by utilizing IFERROR and making the result a blank. From a Power BI standpoint when I use this sheet as a source doc, is it more efficient to make the results zeros or blanks?

1 ACCEPTED SOLUTION
kpost
Super User
Super User

as @sayaliredij said, you can fix them on the Power BI side instead using "DIVIDE" like this:

New Column = DIVIDE([Value A], [Value B], <put 0 or BLANK() here depending on what you want in error case>)

In my opinion, though, as far as Power BI is concerned, whether you do it in Excel or Power BI, best practice is to fill with zeros.  It is more likely to result in desired behavior in the visuals you end up creating using that data set.

View solution in original post

4 REPLIES 4
kpost
Super User
Super User

as @sayaliredij said, you can fix them on the Power BI side instead using "DIVIDE" like this:

New Column = DIVIDE([Value A], [Value B], <put 0 or BLANK() here depending on what you want in error case>)

In my opinion, though, as far as Power BI is concerned, whether you do it in Excel or Power BI, best practice is to fill with zeros.  It is more likely to result in desired behavior in the visuals you end up creating using that data set.

Sounds good. I'll roll with the zeros then. Appreciate it!

sayaliredij
Solution Sage
Solution Sage

HI @chris1234512 ,

 

You can use DIVIDE in DAX formule instead of using '/' . it will handle the error like excel

 

https://learn.microsoft.com/en-us/dax/best-practices/dax-divide-function-operator

 

Thanks,

Sayali





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

Proud to be a Super User!




For my purposes, I'd like to keep the division in excel. Any tips if that's the case?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.