Forum Discussion
Solved
Replacing null value with average of the column
Hi, Looking for help in the following scenario: I want to replace null value in the column with the average value of the column. How can I achieve this? I am interested in using conditional c...
Hi vivran22 ,
let Source = YourTable, #"SetType" = Table.TransformColumnTypes(#"Source ",{{"Sample", type number}}), #"ReplaceAvg" = Table.ReplaceValue(#"SetType",null,List.Average(#"SetType"[Sample]),Replacer.ReplaceValue,{"Sample"}) in ReplaceAvgIs this ok ? I assumed your initial table is YourTable and the column is named "Sample"
Tell me if there is any problem.
Regards,
Etienne
Hi vivran22 ,
let
Source = YourTable,
#"SetType" = Table.TransformColumnTypes(#"Source ",{{"Sample", type number}}),
#"ReplaceAvg" = Table.ReplaceValue(#"SetType",null,List.Average(#"SetType"[Sample]),Replacer.ReplaceValue,{"Sample"})
in
ReplaceAvgIs this ok ? I assumed your initial table is YourTable and the column is named "Sample"
Tell me if there is any problem.
Regards,
Etienne
Hi,
How can I expand the script, to calculate the average by another column? So don't just want the average of the whole column, I want the average by another category (see below fruit type):
| Fruit type | Value | average by fruit type (no need this column, its just a representation) |
| apple | 1 | |
| apple | 2 | |
| apple | null | expected result: 1,5 |
| orange | 3 | |
| orange | 5 | |
| orange | null | expected result: 4 |