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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Matthew5601
New Member

Replace null with Average by Species and Gender

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.

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @Matthew5601 ,

According to your description, I create a sample.

vkalyjmsft_0-1654681645067.png

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.

vkalyjmsft_1-1654681827321.png

3.Add a custom column.

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

Then filter the table with custom=1.

vkalyjmsft_2-1654681938875.png

4.Add a custom column.

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

Get the correct result.

vkalyjmsft_3-1654682025529.png

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

vkalyjmsft_4-1654682094101.png

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.

View solution in original post

2 REPLIES 2
Xiaochyi
Regular Visitor

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

v-yanjiang-msft
Community Support
Community Support

Hi @Matthew5601 ,

According to your description, I create a sample.

vkalyjmsft_0-1654681645067.png

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.

vkalyjmsft_1-1654681827321.png

3.Add a custom column.

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

Then filter the table with custom=1.

vkalyjmsft_2-1654681938875.png

4.Add a custom column.

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

Get the correct result.

vkalyjmsft_3-1654682025529.png

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

vkalyjmsft_4-1654682094101.png

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors