Log In
Register
Home
New Member
March 20, 2021
Offline
Greeting from a newbie to Power Query. I've been able to piece together what I've needed to get by until now.
I need to return the sum of the three consecutive cells with highest value out of a range of cells. See the circled green items in the attached screenshot I provided as an example. This wouldn't necessarily include the MAX value of a specific cell in the given range (red circled item).
I can do this all day long in a spreadsheet with MAX function and overlapping arrays. I can't seem to figure it out with Power Query although I am pretty certain List.Max will be involved somehow.
Help is appreciated.
October 5, 2010
Offline
Hi Ronald,
I've written a custom function to do this and it uses List.Generate to create a list of the totals of each adjacent 3 months
(values as list) =>
let
TheMax = List.Generate
(
()=> [ CurrentMax = 0, M1 = values{0}, M2 = values{1}, M3 = values{2}, i = 2 ],
each [i] - 1 <= List.Count(values),
each [CurrentMax = [M1] + [M2] + [M3], i = [i] + 1, M1 = values{[i] - 2}, M2 = values{[i] - 1}, M3 = values{[i]} ] ,
each [CurrentMax]
)
in
TheMax
This is called from the main query from a custom column
= List.Max(fxMaxMonths(List.RemoveLastN(List.RemoveFirstN(Record.ToList(#"Changed Type"{[Index]})))))
Giving the results in the attached image.
Check out the attached file for the code.
regards
Phil
1 Guest(s)
