How to handle ERROR (NaN, in division /0) values in Get and Transform data model?

[astrbac]

Hello,

I am importing some data from a .csv (Facebook advertising data). While creating the model in the "Get and Transform" dialog window, I add a column which divides two other columns. Since one of them is sometimes empty (null) or 0,00, in the added column I get a NaN error values. 

Finally, this gets me in trouble in the Power pivot section because I cannot calculate, except do a COUNT. Which formula can I enter in the Add column pop up window to handle the error? Something like IFERROR() in Google Sheets or a regular Excel  IF(ISERROR()) formula.

Also, which value to use when an error occurs? Empty string "" might produce problems down the road (in pivot calculations), yero (0,00) is not really correct because some cells are null so we don't know what should go here...

Many thanks!

A.

[v-yuta-msft · Accepted solution]

Hi astrbac,

Are you using DAX to achieve the calculate column? If you are, you can use ISERROR() function or IFERROR() function to avoid the NaN or 0 result. If you are using power query to divide two column, you can consider to use error-handling in power query like try ... otherwise ..., please refer to: https://blog.gbrueckl.at/2013/12/error-handling-in-power-query/.

Regards,

Jimmy Tao