Running Maximum

[Transho]

I have a table with sorted records by Category and Date.

I know how to group and index by Category but simply need a power query code that inserts a column that gets the running maximum per group so the results will be as such.

Thanks for your help.

[sanalytics · Accepted solution]

@Transho 
Apart from, Aliensx solution,
You can also achieve your result by using List.Accumulate function.. Below is the code

let
    Source = Table,
    ConvertingGroupingList = Table.Group( 
Source,{"Category"},{"AllRows",each _}
)[AllRows],
    Result = Table.Combine( 
List.Transform( 
ConvertingGroupingList,(x) =>
Table.FromColumns(
Table.ToColumns(x) &
{List.Select(
List.Accumulate(
x[Value],
{0},
(s,c) => s & {if List.Last(s) < c then c else List.Last(s) }
),each _ <>0 )}, Table.ColumnNames(Source) & {"Running Max"}
) ) )
in
    Result

Also, you can use List.FirstN function for achieveing the result.. Please note List.FirstN is very slow function and you may face performance issue if you are delaing with large dataset.You can use List.Buffer to speed up the performance but, List.Generate and List.Accumulate always preferable. Below is the List.FirstN code

let
    Source = Table,
    Result = Table.Combine( 
Table.Group(
 Table,{"Category"},{"AllRows", each 
let
IndexAdded = Table.AddIndexColumn(_,"Index",1,1),
MaxColumnAdded = Table.AddColumn(IndexAdded,"Running Max", each List.Max( List.FirstN(
List.Buffer(
IndexAdded[Value] ),[Index])) )
in 
MaxColumnAdded
}
)[AllRows]
)[[Date],[Category],[Value],[Running Max]]
in
    Result

Attaching pbix file for your reference where you will find both solution.

Hope this will help you.

Regards,

sanalytics

[Transho]

Hi,

I used the List.Accumulate statement out of your code and it worked like a charm, but i added an additional step to read only one list of the generated lists at every record.

Thanks.

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    rows = List.Buffer(Table.ToList(Source, each _)),
    iterate = List.Generate(
        () => [i = 0, r = rows{0}, max = r{2}],
        (x) => x[i] < List.Count(rows),
        (x) => [
            i = x[i] + 1, 
            r = rows{i}, 
            max = if r{1} <> x[r]{1} or r{2} > x[max] then r{2} else x[max]
        ],
        (x) => x[r] & {x[max]}
    ),
    tbl = Table.FromList(iterate, each _, Table.ColumnNames(Source) & {"Running Max"})
in
    tbl

[Transho]

Hi,

Thanks for your answer and the solution you have provided.

I used to write such codes in SAS but since i am new to power query, this code is a bit complicatd to understand.

During my investigation, i realized that if i grouped then indexed each group, i will be able to use the 

List.Accumulate function with an iterator statement.

But i got stuck in outputing all the records per table.