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 group so the results will be as such.

 

Thanks for your help.

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

4 Replies

  • 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.

  • sanalytics's avatar
    sanalytics
    Icon for Super User rankSuper User
    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

    • Transho's avatar
      Transho
      Frequent Visitor
      10 months ago

      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.