Locate the grouped by min and max value from the source and delete those rows

[kx_xoxo]

Hi there, 

I have the following table and was able to use the GroupBy feature to find out the min and max value for each browser on each day. My goal is to delete the rows with those values in the original table but got stuck. Thank you for your help!

Grouped view: 

[lbendlin · Accepted solution]

Expand the data values again and then filter out the rows where the value matches either the min or the max value in that row. Then remove the max and min columns.

[Omid_Motamedise · Accepted solution]

You can use Table.SelectRows for the sub tables.

[Anonymous · Accepted solution]

Hi,

Thanks for the solution Omid_Motamedise and lbendlin offered, and i want to offer some more information for user to refer to.

hello @kx_xoxo , you can create a blank query and put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck1JT1XSUbLUNzLTNzIwMgGyLfQslGJ1sEoZGuqZ4JKz0DPDo80Yp5wRbiMNDaByzhlF+blQWVOYrKWeOW5JoFYjPLImeqb4DMYraYHHXEN8WoG2WuKRNQN5NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Broswer = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Broswer", type text}, {"Date", type date}, {"Value", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Broswer", "Date"}, {{"Min", each List.Min([Value]), type nullable number}, {"Max", each List.Max([Value]), type nullable number}, {"Data", each _, type table [Broswer=nullable text, Date=nullable date, Value=nullable number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Value"}, {"Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Value] <> [Min]) and ([Value]<>[Max]))
in
    #"Filtered Rows"

Output

Best Regards!

Yolo Zhu

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

[slorin · Accepted solution]

Hi @kx_xoxo 

Table.Combine(
  Table.Group(
   Your_Source, 
   {"Broswer", "Date"}, 
   {{"Data", each Table.RemoveMatchingRows(_, {Table.Max(_, "Value"), Table.Min(_, "Value")})}}
  )[Data]
)

 Stéphane

[kx_xoxo · Accepted solution]

Thanks all! 

I was able to add a helper column filter out the rows matches the max and min values. Thank you again for giving me ideas. 

[slorin · Accepted solution]

Hi @kx_xoxo 

Table.Combine(
  Table.Group(
   Your_Source, 
   {"Broswer", "Date"}, 
   {{"Data", each Table.RemoveMatchingRows(_, {Table.Max(_, "Value"), Table.Min(_, "Value")})}}
  )[Data]
)

 Stéphane

[Anonymous · Accepted solution]

Hi,

Thanks for the solution Omid_Motamedise and lbendlin offered, and i want to offer some more information for user to refer to.

hello @kx_xoxo , you can create a blank query and put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck1JT1XSUbLUNzLTNzIwMgGyLfQslGJ1sEoZGuqZ4JKz0DPDo80Yp5wRbiMNDaByzhlF+blQWVOYrKWeOW5JoFYjPLImeqb4DMYraYHHXEN8WoG2WuKRNQN5NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Broswer = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Broswer", type text}, {"Date", type date}, {"Value", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Broswer", "Date"}, {{"Min", each List.Min([Value]), type nullable number}, {"Max", each List.Max([Value]), type nullable number}, {"Data", each _, type table [Broswer=nullable text, Date=nullable date, Value=nullable number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Value"}, {"Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Value] <> [Min]) and ([Value]<>[Max]))
in
    #"Filtered Rows"

Output

Best Regards!

Yolo Zhu

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

[Omid_Motamedise · Accepted solution]

You can use Table.SelectRows for the sub tables.

[lbendlin · Accepted solution]

Expand the data values again and then filter out the rows where the value matches either the min or the max value in that row. Then remove the max and min columns.