Forum

Power Query Help: R...
 
Notifications
Clear all

Power Query Help: Return sum of the three consecutive cells with highest value out of a range of cells

2 Posts
2 Users
0 Reactions
69 Views
(@rtsmith555)
Posts: 1
New Member
Topic starter
 

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.

screenshot.png

 
Posted : 20/03/2021 1:38 pm
Philip Treacy
(@philipt)
Posts: 1630
Member Admin
 

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  - 1 <= List.Count(values),

              each [CurrentMax = [M1] + [M2] + [M3], i =  + 1, M1 = values{ - 2}, M2 = values{ - 1}, M3 = values{} ] ,

              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

 

themax.png

 
Posted : 22/03/2021 11:44 pm
Share: