Replacing null value with average of the column

[vivran22]

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

[Anonymous]

can you upload some sample data?  if not, basically:

1. Right click the column where you will get the aveage from --> as new query
2. That will give you a list, then under Transform select avearage
3. Back in your main table, use the menu to replace nulls, with say 0 ( can be anything, doesnt matter)
4. 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

[Anonymous]

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

[Anonymous · Accepted solution]

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
    ReplaceAvg

Is this ok ? I assumed your initial table is YourTable and the column is named "Sample"

Tell me if there is any problem. 

Regards, 

Etienne

[kormosb]

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