Group by and Max summarize the max value in each set of data unique values

[Anonymous]

Hi Community,

I was wondering how I can short list my table based on one of the columns with maximum values. I try to clear out as follows:

I have 4 columns each column has a numerical value and one has Quantity:

Col1 = Sequence

Col2 = Tools

Col3 = Quantity in Sequence

Col4 = Last try

the table has 250 rows

So I need to filter this table in a way that only Maximum of Col4(last try) remains and all previous rows removed. I already used Group by to be able to do it but unfirtunately I still have repeated Sequence in Col1:

#Grouped Rows = Table.Group (#"REmoved Blank Rows", {"Sequence", " Tools", "Quantity in Sequence"}, {{"Maximum", each List.Max(#"Last try "), type number}}}

I should note that the Col3 has different values, like it might have 1 or 3 or 4 for the same Sequence(the one with arrow showed in the picture should remain and others removed) but only the one associated with the Maximum Last try should remain and all previous ones should be removed(it doesn't matter if it's the biggest quantity or not )

Any help on how to reach the desired goal?

Thanks

[v-diye-msft]

Hi @Anonymous 

I’ve created the table as below:

Sorted descending and add the index column, then filter the row with index =1  in M code:

#"Sorted Rows" = Table.Sort(Source,{{"Last try", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Index] = 1))
in
#"Filtered Rows"

Best regards,

Dina Ye

[MFelix]

Hi @Anonymous ,

You need to do the Group by column 1 and column 2 and then use the max and the min for the other two values:

Code is below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVR0lFKAWJDILawNFCK1UESNAYJGqMJgmhDkMpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Sequence = _t, Tool = _t, #"Quantity in sequence" = _t, #"Last Try" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sequence", Int64.Type}, {"Tool", type text}, {"Quantity in sequence", Int64.Type}, {"Last Try", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Sequence", "Tool"}, {{"Quantity in Sequence", each List.Min([Quantity in sequence]), type number}, {"Last try.1", each List.Max([Last Try]), type number}})
in
    #"Grouped Rows"

Assuming that column 1 and 2 have the same values.

Regards,

MFelix

[Anonymous]

@MFelix 

Thanks alot! Would you please explain a what is

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVR0lFKAWJDILawNFCK1UESNAYJGqMJgmhDkMpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Sequence = _t, Tool = _t, #"Quantity in sequence" = _t, #"Last Try" = _t])

and how I can manipulate to use with my own file?

Cheers

[MFelix]

That row is simply my input table to simulate your data.

You need to get the step of the group by.

Regards,
MFelix

[Anonymous · Accepted solution]

@MFelix,

Actually I used the merge column method to done my job but thanks very much for your hint