Forum Discussion
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:
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)
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.
3 Replies
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.
