Rolling min and max column based on column

[guto.bartinho]

Hello, since I am new to PowerBI and I have a table with 100k+ rows with different products and prices, I would appreciate any help getting the rolling min and max value column for price in PowerQuery.

Here's the example table: Rolling min and roll max (grey) is what I am looking for in Power Query, it will be based on product and time will be sorted ascending.

Example to copy paste

Product	Time	Price
Product1	10:02:00​	14,66
Product1	10:03:00​	14,67
Product1	10:04:00​	14,64
Product1	10:05:00​	14,63
Product1	10:06:00​	14,65
Product1	10:07:00​	14,67
Product1	10:08:00​	14,65
Product1	10:09:00​	14,65
Product1	10:10:00​	14,61
Product1	10:11:00​	14,58
Product1	10:12:00​	14,58
Product1	10:13:00​	14,60
Product1	10:14:00​	14,60
Product1	10:15:00​	14,60
Product1	10:16:00​	14,63
Product1	10:17:00​	14,63
Product 2	10:04:00​	14,62
Product 2	10:05:00​	14,62
Product 2	10:06:00​	14,62
Product 2	10:07:00​	14,65
Product 2	10:08:00​	14,67
Product 2	10:09:00​	14,67
Product 2	10:10:00​	14,67
Product 2	10:11:00​	14,66
Product 2	10:12:00​	14,67
Product 2	10:13:00​	14,66
Product 2	10:14:00​	14,65
Product 2	10:15:00​	14,66
Product 2	10:16:00​	14,66
Product 2	10:17:00​	14,71
Product 2	10:18:00​	14,70
Product 2	10:19:00​	14,68
Product 2	10:20:00​	14,69
Product 2	10:21:00​	14,70

 

[Matt Allington]

What are you going to do with the new column after you creat it in Power Query? You *can* do it this way, but I think a better solution is to load the raw data to the data model and use DAX to return the result In a pivot table. The DAX is pretty straight forward

rolling min = calculate(min(table[price]),filter(all(table),table[time] <= max(table[time])))

 

[JEC]

Have you tried that? I miss the product reference. I thought about the EARLIER function

 

[guto.bartinho]

What are you going to do with the new column after you creat it in Power Query? You *can* do it this way, but I think a better solution is to load the raw data to the data model and use DAX to return the result In a pivot table. The DAX is pretty straight forward

rolling min = calculate(min(table[price]),filter(all(table),table[time] <= max(table[time])))

Thank you for your help! I have 85 csv's files (each 20k rows) that I load into Power Query, remove unnecessary columns and sort by product. After that I load it as a table into Excel.

I tried adding it to data model and using the DAX formula provided but I think it's considering entire Product column, it should reference each Product (@JEC). Can you help me how to add that to the condition?
Since it's in Data Model can I output it as a table? It only appear Pivot Table format...


Rolling min output in Product 2 considered Product 1(yellow), Product 1 should consider Product 1 prices only, Product 2 only Product 2.

 

[JEC]

But if you work with PoweBI you could load it directly into your data model jn PowerBI.

So, why Excel?

 

[JEC]

Here a PQ solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    grp = Table.Group(Source, {"Product"},
             {{
                "Count", each let t = _ in                   
                 List.Accumulate({"Min", "Max"},t, (state,curr) =>                 
                     Table.AddColumn(state, curr, each                 
                        List.Accumulate(state[Price], {}, (s,c)=> 
                          if curr = "Min" then
                              if c < List.Min(s) or s = {} then s & {c} else s & {List.Min(s)}
                            else
                          if c > List.Max(s) or s = {} then s & {c} else s & {List.Max(s)})                           
                     )
                  )
             }}
         ),

    arr = {0..List.Count(grp[Product])-1},
    min = List.Accumulate(arr, {} ,(s,c)=> s & grp{c}[Count][Min]{0}),
    max = List.Accumulate(arr, {} ,(s,c)=> s & grp{c}[Count][Max]{0}),
    out = Table.FromRows(List.Zip({Source[Product],Source[Time],Source[Price],min,max}), {"Product", "Time", "Price", "RollMin", "RollMax"})
in
    out