I'm adding a division column to calculate profit %. I have a SaleAmt$ and GP$ columns. (GP/Sales=%Profit)
First try, I grabbed the GP$ column, then the SaleAmt$ column, then Add column & selected Divide. In rows where there is $0 sales and $0 profit I'm getting NaN, which is throwing ####...... into my table. System would not replace Nan with 0. Other rows are fine and import fine into the spreadsheet.
Second try, I grabbed the GP$ column, then the SaleAmt$ column, then selected Percent of. I get the same Nan error, but it displays 20 instead of .20, so when I load to the table the formatting to include the % sign gives me 2000%
I know in a worksheet I can use the IfError function but I got big NoNo doing that in PowerQuery. Am I missing something? I think this is an often used formula but I'm striking out on a web search.
Thanks in advance!
Pam
After some consideration, I'm wondering if a better option would be to do the profit calc in an added column on the table after I load. Or maybe an added column on my pivot table.
Hi Paul,
Add a new column in Power Query with a simple formula instead of using the Divide or Percent Of options. This way you will be able to decide what to return in case of errors:
if [Column2]=0 or [Column1]=0 then 0 else [Column2]/[Column1]
If Column1 is zero, you will get the Infinity error, NaN will be returned when both columns have a zero.
