Forum Discussion
Removing NaN and Infinities in Power Query
Hi Folks,
I've read a few possible solutions to this, but can't work out how to add them to the default formulas for add column etc.
Staff enter data on the My Day Entry worksheet, it then gets PQ'd into a couple of other worksheets.
The issue I'm having is on the example data tab. Excel doesn't like NaN or Infinities as a value. (Division column creates them when response is 0.)
I can't work out syntax to do an if/else to do 0 as null.
Any help appreciated.
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.
- Thank you Riny and L z. Will try shortly.
- I couldn't add the above exactly with a copy and paste into the step in the regular editor, but but could follow your syntax above enough to use the Advanced editor to add in the method by using the text from "each if.." onwards. Thanks.
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%.
Alternatively:
#"Inserted Division" = Table.AddColumn(#"Filtered Rows", "Division", each Value.Divide( [Effective Praise], if [Practice Teaching] = 0 then null else [Practice Teaching] ), type number ),
