Calculate the avg of the 1st, 2nd ......7th smallest number for every 250 rows in a rolling manner

[Blue1988]

I was able to calculate the 3rd smallest number for every 250 rows in a rolling manner by following PQ codes. But I also need to create another column to calculate the average of the 1st, 2nd, 3rd......7th smallest numbers for every 250 rows in a rolling manner . Can anybody help me? Thank you!

Buffer = Table.Buffer(SortedRows),
values = Buffer[Value],
date = Buffer[Date],
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({date,values, rolling250_3rd_smallest}, {"Date", "Value", "rolling250_3rd_smallest "}),
#"Changed Type" = Table.TransformColumnTypes(tbl,{{"rolling250_3rd_smallest ", Int64.Type}, {"Date", type date}})

in
#"Changed Type"

[lbendlin]

Are you sure this is something you want to do in Power Query? Is the data immutable? Can the result be impacted by filter choices?

[Blue1988]

I did that in Power query by repeating the codes to create smallest 1, 2,....7. But isn't there a smarter way to do that? For example loop from 1 to 7 to calculate those smallest numbers.

rolling250_1st_smallest = List.Repeat({null}, 249) &
List.Generate(
() => [i = 0, small = List.MinN(List.Range(values, 0, 250), 1), small_1 = small{0}],
(x) => x[i] < List.Count(values) - 249,
(x) => [i = x[i] + 1, small = List.MinN(List.Range(values, i, 250), 1), small_1 = small{0}],
(x) => x[small_1]
),

rolling250_2nd_smallest = List.Repeat({null}, 249) &
List.Generate(
() => [i = 0, small = List.MinN(List.Range(values, 0, 250), 2), small_2 = small{1}],
(x) => x[i] < List.Count(values) - 249,
(x) => [i = x[i] + 1, small = List.MinN(List.Range(values, i, 250), 2), small_2 = small{1}],
(x) => x[small_2]
),
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]
),

rolling250_4th_smallest = List.Repeat({null}, 249) &
List.Generate(
() => [i = 0, small = List.MinN(List.Range(values, 0, 250), 4), small_4 = small{3}],
(x) => x[i] < List.Count(values) - 249,
(x) => [i = x[i] + 1, small = List.MinN(List.Range(values, i, 250), 4), small_4 = small{3}],
(x) => x[small_4]
),

rolling250_5th_smallest = List.Repeat({null}, 249) &
List.Generate(
() => [i = 0, small = List.MinN(List.Range(values, 0, 250), 5), small_5 = small{4}],
(x) => x[i] < List.Count(values) - 249,
(x) => [i = x[i] + 1, small = List.MinN(List.Range(values, i, 250), 5), small_5 = small{4}],
(x) => x[small_5]
),

rolling250_6th_smallest = List.Repeat({null}, 249) &
List.Generate(
() => [i = 0, small = List.MinN(List.Range(values, 0, 250), 6), small_6 = small{5}],
(x) => x[i] < List.Count(values) - 249,
(x) => [i = x[i] + 1, small = List.MinN(List.Range(values, i, 250), 6), small_6 = small{5}],
(x) => x[small_6]
),

rolling250_7th_smallest = List.Repeat({null}, 249) &
List.Generate(
() => [i = 0, small = List.MinN(List.Range(values, 0, 250), 7), small_7 = small{6}],
(x) => x[i] < List.Count(values) - 249,
(x) => [i = x[i] + 1, small = List.MinN(List.Range(values, i, 250), 7), small_7 = small{6}],
(x) => x[small_7]
),

tbl = Table.FromColumns({date,values, rolling250_1st_smallest ,rolling250_2nd_smallest ,rolling250_3rd_smallest,
rolling250_4th_smallest,rolling250_5th_smallest,rolling250_6th_smallest, rolling250_7th_smallest},
{"Date", "FI_Value", "1st", "2nd" , "3rd","4th", "5th","6th", "7th"}),
#"Added Custom" = Table.AddColumn(tbl, "Custom", each List.Average(List.Select({[1st],[2nd],[3rd],[4th],[5th],[6th],[7th]},each _<>0)), type number),

[dufoq3]

Hi @Blue1988, check this:

If you want to select only numbers except 0 like you did before:

you can replace my GeneratedRollingAverage step with this:

= List.Generate(
        ()=> [ x = 1, helper = List.LastN(List.FirstN(BufferedValue, x), 250), y = List.Last(List.MinN(helper, 3)), z = List.Average(List.RemoveItems(List.MinN(helper, 7), {0})) ],
        each [x] <= List.Count(BufferedValue),
        each [ x = [x]+1, helper = List.LastN(List.FirstN(BufferedValue, x), 250), y = List.Last(List.MinN(helper, 3)), z = List.Average(List.RemoveItems(List.MinN(helper, 7), {0})) ],
        each [3rd smallest = [y], Min 7 avg = [z]]
)

Whole code (inluded zeros)

let
  Source = Table.Repeat(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VVLJDcMwDNsl7z4qW4c9S5D91yhgMgX1EnSQomjf92X7ej73NccJ25DVCYHe8BPSdSQBWMj8CwBqEyStBhBLrCXmMF4gDNPVBdxAzwBY6FE5ZTl7S4ODrLgIa3NrVjwnddFqxaAbvF/XeahDgySh84WwvHlDP5aebgyA+1SZ76LUkY2RAC7pI1rRjpxqHM3Jhm6M3tSRA2+XHARqbs2MktU1fiVauZvNwYzqSiWT2Xmb/TmfHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]), 10),
    ChangedType = Table.TransformColumnTypes(Source,{{"Value", type number}}),
    BufferedValue = List.Buffer(ChangedType[Value]),
    // Rolling average for min 7 values of last 250 rows
    GeneratedRolingAverage = List.Generate(
        ()=> [ x = 1, helper = List.LastN(List.FirstN(BufferedValue, x), 250), y = List.Last(List.MinN(helper, 3)), z = List.Average(List.MinN(helper, 7)) ],
        each [x] <= List.Count(BufferedValue),
        each [ x = [x]+1, helper = List.LastN(List.FirstN(BufferedValue, x), 250), y = List.Last(List.MinN(helper, 3)), z = List.Average(List.MinN(helper, 7)) ],
        each [3rd smallest = [y], Min 7 avg = [z]]
),
    ToTable = Table.FromColumns(Table.ToColumns(ChangedType) & Table.ToColumns(Table.FromRecords(GeneratedRolingAverage)), Value.Type(ChangedType & #table(type table[Rolling 250 3rd smallest = number, Rolling 250 min 7 avg = number], {}) ))
in
    ToTable