Forum Discussion
Removing NaN and Infinities in Power Query
As an example, change the first "Inserted Division" step to:
Table.AddColumn(#"Filtered Rows", "Division", each if [Practice Teaching] <> 0 then [Effective Praise] / [Practice Teaching] else 0, type number)
This will test if [Practice Teaching] is not equal to zero, then perform the division else return zero. You need to do something similar to the "Inserted Percent Of" step. Do that and all following calculations will go just fine.
As an example, change the first "Inserted Division" step to:
Table.AddColumn(#"Filtered Rows", "Division", each if [Practice Teaching] <> 0 then [Effective Praise] / [Practice Teaching] else 0, type number)
This will test if [Practice Teaching] is not equal to zero, then perform the division else return zero. You need to do something similar to the "Inserted Percent Of" step. Do that and all following calculations will go just fine.
Riny_van_Eekelen my next cheeky follow up is around the presentation of the % numbers.
You'll note how they present after the Round up step. eg "3400.00%" I have had to change them from numbers in subsequent steps to make them present in a more user friendly way, but that means I can't then use conditional formatting on the data. I haven't seen a way to present them with no decimals and no extra 00s without losing number format. Any help definitely appreciated.
davidmaddock54 Well, you need to remover the " * 100 " bit in the step where you calculate the percentage. That will give you a number like 0.9166666667. Then change to data type to % to get 91.67%. And then round to zero decimals to get to 92%.
- Faceplam.gif. Sometimes when the question is answered, it seems so obvious.
Thank you.