Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.