Forum Discussion
Solved
Power Query - Add an Average Column from other columns in the table excluding zeros from Avg Calc
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: ...
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)The easiest way is to replace null with 0 in next step.
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)
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:
The easiest way is to replace null with 0 in next step.