SOLVED

Removing NaN and Infinities in Power Query

Brass Contributor

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.

 

7 Replies
best response confirmed by davidmaddock54 (Brass Contributor)
Solution

 

@davidmaddock54

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.

Hi @davidmaddock54 

 

Alternatively:

#"Inserted Division" = Table.AddColumn(#"Filtered Rows", "Division", each
  Value.Divide(
    [Effective Praise],
    if [Practice Teaching] = 0 then null else [Practice Teaching]
  ),
  type number
),
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_0-1704147895990.png

 

@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.
1 best response

Accepted Solutions
best response confirmed by davidmaddock54 (Brass Contributor)
Solution

 

@davidmaddock54

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.

View solution in original post