Forum Discussion
Community ChampionReplacing 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 column or something of this sort.
Regards,
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
6 Replies
can you upload some sample data? if not, basically:
- Right click the column where you will get the aveage from --> as new query
- That will give you a list, then under Transform select avearage
- Back in your main table, use the menu to replace nulls, with say 0 ( can be anything, doesnt matter)
- Then in the menu bar, change where it says 0, to name of list from #2
That should put the average where nulls used to be.
- vivran22
Anonymous
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
