Filter for lowest n rows by category

[tencentgolf]

Hello,

I am trying to figure out how to obtain up to the lowest 4 rows by category on Power Query.  I am trying to use sort, select and firstN but failing miserably. Here is an example:

Name	R1
Court	67
Court	68
Court	67
Court	72
Court	85
Tom	73
Tom	65
Tom	66
Mike	66
Mike	67
Mike	66
Mike	80
Mike	65

Where output would be:

Name	R1
Court	67
Court	68
Court	67
Court	72
Tom	73
Tom	65
Tom	66
Mike	66
Mike	67
Mike	66
Mike	65

Filtered out are:

Court	85
Mike	80

Any thoughts? Thank you in advance.

[AlB · Accepted solution]

Hi @tencentgolf 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4vLSpR0lEyM1eK1UHiWqByUWXNjVC4FqZgbkh+LkjOGIljhixjZgbm+GZmp2LyzHHLWRigyAGNjAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, R1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"R1", Int64.Type}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"R1", each List.FirstN(List.Sort([R1]),4) }}),
    #"Expanded Count" = Table.ExpandListColumn(#"Grouped Rows", "R1")
in
    #"Expanded Count"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[CNENFRNL · Accepted solution]

Hi, @tencentgolf , you might want to try

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4vLSpR0lEyM1eK1UHiWqByUWXNjVC4FqZgbkh+LkjOGIljhixjZgbm+GZmp2LyzHHLWRigyAGNjAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, R1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"R1", Int64.Type}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Lowest 4", each List.MinN([R1],4)}}),
    #"Expanded All" = Table.ExpandListColumn(#"Grouped Rows", "Lowest 4")
in
    #"Expanded All"

[AlB · Accepted solution]

Hi @tencentgolf 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4vLSpR0lEyM1eK1UHiWqByUWXNjVC4FqZgbkh+LkjOGIljhixjZgbm+GZmp2LyzHHLWRigyAGNjAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, R1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"R1", Int64.Type}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"R1", each List.FirstN(List.Sort([R1]),4) }}),
    #"Expanded Count" = Table.ExpandListColumn(#"Grouped Rows", "R1")
in
    #"Expanded Count"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[tencentgolf]

Hi Sorry, what is the Json piece?

[mahoneypat]

The json piece just means they copied your example data into their query to demonstrate how to do it.  Create a new blank query, open the Advanced Editor, and paste their M code over the default text there.

Regards,

Pat