Forum Discussion
Replacing null value with average of the column
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
Anonymous
I won't be able to share the exact data file, but following is what I am looking for:
This is the sample data:
| Sample 1 |
| 1 |
| 2 |
| 3 |
| 4 |
| null |
| 5 |
The requirement is that the null should be replaced as 3 (average of the dataset)
and in case there are addition in the table then null should update accordingly:
| Sample 2 |
| 1 |
| 2 |
| 3 |
| 4 |
| null |
| 5 |
| 6 |
| 7 |
In Sample 2, the null should be replaced as 4.
Hope this helps.
Rgds,
Vivek
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
