Forum Discussion
Community ChampionReplacing 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
Community ChampionAnonymous
Thank you, Nick for your response. The challenge with the resposne you shared is that it is not dynamic. If in case, there are additional rows of data, then the average value of the column will change. The need is to make it dynamic, ie using Power Query/M I can calculate the average of the column and then replaces all the null values with it instead of calculating average manually.
Regards,
Vivek
sure, DAX and M can pretty much do anything within reason. Can you upload a sample pbix file?
- vivran22
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
kormosbHelper III
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