Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
kormosb
Helper III
Helper III

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

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 typeValueAverage by fruit type (no need this column, it's just a representation)
apple1 
apple 2 
applenullexpected result: 1,5
orange3 
orange5 
orangenullexpected result: 4

 

Thanks in advance,

 

Benjamin

2 ACCEPTED SOLUTIONS
PhilipTreacy
Super User
Super User

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"

 

avgfruit.png

 

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

avgfruit2.png

 

Regards

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

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"})

vjaneygmsft_0-1636438604882.png

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

View solution in original post

4 REPLIES 4
PhilipTreacy
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


kormosb
Helper III
Helper III

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

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"})

vjaneygmsft_0-1636438604882.png

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
Super User
Super User

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"

 

avgfruit.png

 

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

avgfruit2.png

 

Regards

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors