Get the third smallest number for every 250 rows in a rolling manner using Power Query

[Blue1988]

I need to calculate the third smallest number for every 250 rows in a rolling manner using Power Query. Doing it by excel is easy, we just need to scroll down the formula on each date like below. However, how to do it in Power query? Thanks!

[AlienSx · Accepted solution]

let
    // replace values with a list of your numbers
    values = List.Buffer(List.Transform({1..10000}, (x) => Number.Random())),
    rolling250_3rd_smallest = List.Repeat({null}, 249) & 
        List.Generate(
            () => [i = 0, small = List.MinN(List.Range(values, 0, 250), 3), small_3 = small{2}],
            (x) => x[i] < List.Count(values) - 249,
            (x) => [i = x[i] + 1, small = List.MinN(List.Range(values, i, 250), 3), small_3 = small{2}],
            (x) => x[small_3]
        ),
    tbl = Table.FromColumns({values, rolling250_3rd_smallest}, {"values", "small_3_250"})
in
    tbl

[Blue1988]

Hi Alien,

Thank you very much for providing the solution. It works!

I have another challenge, which is to calculate the 1st , 2nd, 3rd.......7th smallest number and take the average. Based on your code, I think I should create something like rolling250_2nd_smallest, rolling250_min, rolling250_3rd_smallest...........rolling250_7th_smallest and create a calculated column to take the average of the 7 columns. But I am wondering whether there is a nicer and elegant way to do that?

Thanks!

[Anonymous]

Hi @Blue1988 ,

Here is my sample data:
Value

25
24
23
22
21
20
19
18
17
16
15
14
13
12
11
10
9
8
7
6
5
4
3
2
1
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

Since 250 rows of data is too big, here's an example of finding the third smallest number every 25 rows:
Put all of the M function into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc6xEYNADETRXogdWFphcC0M/bdhxu+if4H2zV3X1vt2v56MRFpK3v/UV0455COUohSlKEUpCoSBIADs1yfMvB21RZs3K+C4jFkYWSA9lFBCCWUoQxnKUIYy64+UoQxlKPuj3D8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Group", each Number.RoundDown(([Index] - 1) / 25) + 1),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Value", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Group"}, {{"Rank", each Table.AddIndexColumn(_, "IndexbyGroup", 1, 1, Int64.Type), type table}}),
    #"Expanded Rank" = Table.ExpandTableColumn(#"Grouped Rows", "Rank", {"Value", "Index", "IndexbyGroup"}, {"Value", "Index", "IndexbyGroup"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Rank",{"Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([IndexbyGroup] = 3))
in
    #"Filtered Rows"

And the final output is as below:

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

[Blue1988]

What I need is the rolling 250 days third smallest trend. E.g:

Index   3rd Smallest

1

2

.

.

.

250          49

251          80

252          90

.                .

.                .

.                .

4361       120

[Blue1988]

Can anybody help me on this issue? Is it possible to be done by power query? Thanks!

[Anonymous]

Hi @Blue1988 ,

Sorry I misunderstood you, please try the method (still use 25 as an example):
Here is my sample data:
IndexValue

1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
21	21
22	22
23	23
24	24
25	25
26	26
27	27
28	28
29	29
30	30
31	31
32	32
33	33
34	34
35	35
36	36
37	37
38	38
39	39
40	40
41	41
42	42
43	43
44	44
45	45
46	46
47	47
48	48
49	49
50	50

Please first create a blank query:

And put all of the M function into the advanced editor:

let 
    ThirdSmallestInLast25Rows = (tbl as table, idx as number) as any =>
    let
        RangeToConsider = Table.SelectRows(tbl, each [Index] <= idx and [Index] > idx - 25),
        SortedRange = Table.Sort(RangeToConsider, {"Value", Order.Ascending}),
        Result = if Table.RowCount(SortedRange)<25 then null else SortedRange{2}[Value]
    in 
        Result
in 
    ThirdSmallestInLast25Rows

This will create a new function:

And then back to the data table, and add an Invoke Custom Function:

And the final output is as below:
IndexValueNew Value

1	1	null
2	2	null
3	3	null
4	4	null
5	5	null
6	6	null
7	7	null
8	8	null
9	9	null
10	10	null
11	11	null
12	12	null
13	13	null
14	14	null
15	15	null
16	16	null
17	17	null
18	18	null
19	19	null
20	20	null
21	21	null
22	22	null
23	23	null
24	24	null
25	25	3
26	26	4
27	27	5
28	28	6
29	29	7
30	30	8
31	31	9
32	32	10
33	33	11
34	34	12
35	35	13
36	36	14
37	37	15
38	38	16
39	39	17
40	40	18
41	41	19
42	42	20
43	43	21
44	44	22
45	45	23
46	46	24
47	47	25
48	48	26
49	49	27
50	50	28

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