Power query - Replacing null value with average of the column BY ANOTHER COLUMN (TYPE)

[kormosb]

Hi,

I would like to expand the original query mentioned in this post:

https://community.powerbi.com/t5/Power-Query/Replacing-null-value-with-average-of-the-column/m-p/755...

Here is the original script:

let
    Source = YourTable,
    #"SetType" = Table.TransformColumnTypes(#"Source ",{{"Sample", type number}}),
    #"ReplaceAvg" = Table.ReplaceValue(#"SetType",null,List.Average(#"SetType"[Sample]),Replacer.ReplaceValue,{"Sample"})
in
    ReplaceAvg

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, it's just a representation)
apple	1
apple	2
apple	null	expected result: 1,5
orange	3
orange	5
orange	null	expected result: 4

Thanks in advance,

Benjamin

[PhilipTreacy · Accepted solution]

Hi @kormosb 

Download sample PBIX file with the following examples.

I'm not really sure why you'd want to do this.  You're mixing up different types of values, individual values and averages, in the same column.

But you can do it with this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lEyVIrVQfCMUHh5pTk5YIH8osS8dJCIMSrXFJULUR8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Group(#"Changed Type", {"Fruit"}, {{"Avg", each List.Average([Value]), type nullable number}})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each [Custom][Avg]{List.PositionOf([Custom][Fruit], [Fruit])}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Values", each if [Value] is null then [Custom.1] else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Value", "Custom", "Custom.1"})
in
    #"Removed Columns"

If you bring the data into Power BI you can use a table to show the average for each fruit without writing any code.

Regards

Phil

[PhilipTreacy]

Hi @kormosb 

My code does replace null values with the average for the type as requested.

Why is adding 3 intermediate columns and then deleting them an issue? 

The script you reference uses the Table.ReplaceValue function.  This replaces a single value in a column with another value.  It's not designed to replace multiple indentical values with different values.  So if you wanted to replace null only for apple with x, and null for orange with y, it may well be possible but I think would require more complicated coding than I've already done involving lots of list and sub-list manipulation.

Regards

Phil

[kormosb]

Hi,

I would like to use this srcipt, in case I have missing values in the data and would like to replace the missing values with the average by type. 

Can we solve this without adding those additional columns in your solution?

Kind regards,

Benjamin

[v-janeyg-msft · Accepted solution]

Hi, @kormosb 

You can add a custom step in Power Query.

Like this:

= Table.ReplaceValue(#"Changed Type", each [Value], each if [Value]=null then let name = [Fruit Type], avg= List.Average(Table.SelectRows(#"Changed Type",
(x)=>x[Fruit Type]=name)[Value]) in avg else [Value], Replacer.ReplaceValue,{"Value"})

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[PhilipTreacy · Accepted solution]

Hi @kormosb 

Download sample PBIX file with the following examples.

I'm not really sure why you'd want to do this.  You're mixing up different types of values, individual values and averages, in the same column.

But you can do it with this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lEyVIrVQfCMUHh5pTk5YIH8osS8dJCIMSrXFJULUR8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Group(#"Changed Type", {"Fruit"}, {{"Avg", each List.Average([Value]), type nullable number}})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each [Custom][Avg]{List.PositionOf([Custom][Fruit], [Fruit])}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Values", each if [Value] is null then [Custom.1] else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Value", "Custom", "Custom.1"})
in
    #"Removed Columns"

If you bring the data into Power BI you can use a table to show the average for each fruit without writing any code.

Regards

Phil