Forum Discussion

Transho's avatar
Transho
Frequent Visitor
10 months ago
Solved

Running Maximum

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...
Show and Tell
Tips and Tricks
  • sanalytics's avatar
    sanalytics
    10 months ago

    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

AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
10 months ago
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's avatar
    Transho
    Frequent Visitor
    10 months ago

    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.