Power Platform Community Forum Thread Details

How to fill null values in column with median value for each category?

I have this table (there columns I need):

Screenshot 2024-03-28 123848.png

I need to replace null values in original_eur column with median value for each group (group columns are event_id and category).

let
Source = Csv.Document(File.Contents("E:\Kwork\Copy_Ticombo_parse_data.csv"),[Delimiter=";", Columns=40, Encoding=1251, QuoteStyle=QuoteStyle.None]),

#"Upper heads" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

#"Changed types" = Table.TransformColumnTypes(#"Upper heads",{{"Name", type text}, {"id", type text}, {"entity_id", type text}, {"competition_name", type text}, ... etc }),

#"Add column with median values" = Table.AddColumn(#"Changed types", "median_values", each Table.SelectRows(
Table.Group(
#"Changed types",
{"event_id", "category"},
{"median", each List.Median(_[original_eur]), Int64.Type}
),
each _ = [event_id = "event_id", category = "category"]
))
in
#"Add column with median values"

At last step:

1) I make grouped table with medians;

At this stage all works.

But how select right value for each row?

Please try the below:

let
Source = Csv.Document(File.Contents("E:\Kwork\Copy_Ticombo_parse_data.csv"),[Delimiter=";", Columns=40, Encoding=1251, QuoteStyle=QuoteStyle.None]),
#"Upper heads" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed types" = Table.TransformColumnTypes(#"Upper heads",{{"Name", type text}, {"id", type text}, {"entity_id", type text}, {"competition_name", type text}, ... etc }),
#"Grouped for Median" = Table.Group(#"Changed types", {"event_id", "category"}, {"median", each List.Median([original_eur]), type nullable number}),
#"Joined with Median" = Table.Join(#"Changed types", {"event_id", "category"}, #"Grouped for Median", {"event_id", "category"}, JoinKind.LeftOuter),
#"Replaced Nulls" = Table.TransformColumns(#"Joined with Median", {"original_eur", each if _ = null then [median] else _, type nullable number}),
#"Removed Median Column" = Table.RemoveColumns(#"Replaced Nulls",{"median"})
in
#"Removed Median Column"

Group for Median: First, create a table grouped by event_id and category with the median of original_eur calculated for each group.

Join with Median: Then, perform a left outer join of the original table with the median table on event_id and category. This step merges the median value into each corresponding row of the original dataset.

Replace Nulls: After the join, use Table.TransformColumns to replace null values in the original_eur column with the corresponding median value.

Remove Median Column: Finally, remove the temporary median column as it's no longer needed after replacing the null values.

Thanks,

Quick Links

Leaderboard > Power Apps - Power Query