Replace null with Average by Species and Gender

[Matthew5601]

I have been studying Power Query and to do so I referenced some of Gil Raviv’s blogs/vlogs on DataChant - Your next stop in mastering Power Query and Power BI.

The Webinar Followup: Be a Full Stack #PowerBI Jedi - DataChant page is specifically concerned with learning how to iteratively load multiple pages from a website.

While completing the project, I came upon the need to replace missing height and weight values set to null with the average height or weight assigned to that species/gender for a particular character.

My methodology for assigning these average heights/weights was to remove the characters with missing heights or weights, group the characters by species/gender, then calculate the average heights/weights. This was all done via M in the “Ht/Wt by Species/Gender” query.

Once the sub-table “Ht/Wt by Species/Gender” was created, I pulled the full character table back and hoped that I could just “replace values” null with the average height or weight.  I didn’t know how to do that. That is my question;  Is there a way to pull the average value into a null value in the full table?

I accomplished my objective by Cartesian product in the “All but no height or mass” query … which is not a viable option once my projects and tables become much larger, which they will.

Any advice or have you accomplished the objective without using a Cartesian product?  I have to believe that there is a function that could be coded in M for this.

[Xiaochyi]

Hi , may i know if possible to add in the filter value to only include amount > certain value for list.average?

[v-yanjiang-msft · Accepted solution]

Hi @Matthew5601 ,

According to your description, I create a sample.

Here's my solution.

1.Add a custom column.

Table.Group(#"Changed Type", {"Specie"}, {{"Average", each List.Average([Height]), type nullable number}})

2.Get tables in the new column, expand Specie and Average columns.

3.Add a custom column.

if [Specie]=[Custom.Specie] then 1 else 0

Then filter the table with custom=1.

4.Add a custom column.

if [Height] = null then [Custom.Average] else [Height]

Get the correct result.

Then remove other columns and rename the custom column, get the result.

Here's the complete code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXLJTweShgZKsToQAefEEiBphBDwyC8qTgXSYAEnICMoMSkpE6TI2AAu5pZfASRNTOECIZnpqUVA2hShJjgjNbUAbFAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Specie = _t, Animal = _t, Height = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Specie", type text}, {"Animal", type text}, {"Height", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Group(#"Changed Type", {"Specie"}, {{"Average", each List.Average([Height]), type nullable number}})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Specie", "Average"}, {"Custom.Specie", "Custom.Average"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Specie]=[Custom.Specie] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = 1)),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Custom.1", each if [Height] = null then [Custom.Average] else [Height]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Height", "Custom.Specie", "Custom.Average", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Height"}})
in
    #"Renamed Columns"

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.