Power Query - Add an Average Column from other columns in the table excluding zeros from Avg Calc

[SandraJ]

I'm new to Power Query and need help with adding an average column that would average a few columns in the table but exclude column with values of zero from the average calcuation. 

For example:

Column 1         Column 2        Avg Column

  5                       0                     5

How to I modify the M code below so that it exclude zeros from average calculation:

= Table.AddColumn(#"Renamed Columns1", "Average", each List.Average({[#"Column 1"], [Column 2]}), type number)

[Jakinta · Accepted solution]

You can try this

= Table.AddColumn(#"YourTable", "Custom", each List.Average(List.RemoveMatchingItems({[Column1],[Column2],[Column3]},{0})), type number)

or

= Table.AddColumn(#"YourTable", "Custom", each List.Average(List.Select({[Column1],[Column2],[Column3]},each _<>0)), type number)

[Jakinta · Accepted solution]

You can try this

= Table.AddColumn(#"YourTable", "Custom", each List.Average(List.RemoveMatchingItems({[Column1],[Column2],[Column3]},{0})), type number)

or

= Table.AddColumn(#"YourTable", "Custom", each List.Average(List.Select({[Column1],[Column2],[Column3]},each _<>0)), type number)

[SandraJ]

Thank you so much Jakinta. The solution worked perfectly. Much appreciated.  On more question, if the Avg Column result is "null" because both Column 1 & 2 have zero value, how to I modify the M code so that the Avg Column value is the number "0" instead of the word "null"? Example below:

[Jakinta · Accepted solution]

The easiest way is to replace null with 0 in next step.