Forum Discussion

Brass Contributor

Dec 26, 2023

Solved

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 ge...

My Day demo.xlsx57 KB

excel

Riny_van_Eekelen
Dec 27, 2023

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.

Riny_van_Eekelen

Platinum Contributor

Dec 27, 2023

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.

davidmaddock54

Brass Contributor

Jan 01, 2024

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.