Forum Discussion
Error loading table but query editor shows no error rows
Hello ROKRI
first of all I would reccomend to use this formula, instead of yours
= Table.AddColumn(#"Added Receivable Balance", "Current FY Actual ICR %", each if [FY20 Actual Direct]=0 then null else ([2020 Expenditure]-[FY20 Actual Direct])/[FY20 Actual Direct], type number)otherwise you have a mixture of data-type text and number.
In Power Query you can use the try otherwise syntax to get the errors, but I think the NaN is not covered here.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
I solved the 2 issues related to blank error tables because some calculations were returning NaN, though, none of these cells were showing as error. The error table was blank. I wrote the following formula to avoid the NaN "= Table.AddColumn(#"Added Receivable Balance", "Current FY Actual ICR %", each if [FY20 Actual Direct]=0 then "null" else ([2020 Expenditure]-[FY20 Actual Direct])/[FY20 Actual Direct])".
Two questions:
1) Why does PBI not show NaN as an error in the error table?
2) How would I write it as an iferror(calculation,0) formula? I've read a number of posts and it seems to be complicated m code though I imagine there's got to be an easier way.
Thank you,
Russ
Hello ROKRI
first of all I would reccomend to use this formula, instead of yours
= Table.AddColumn(#"Added Receivable Balance", "Current FY Actual ICR %", each if [FY20 Actual Direct]=0 then null else ([2020 Expenditure]-[FY20 Actual Direct])/[FY20 Actual Direct], type number)otherwise you have a mixture of data-type text and number.
In Power Query you can use the try otherwise syntax to get the errors, but I think the NaN is not covered here.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy