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
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.
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