Help with a formula

[James_White_199]

Hi 

I need help creating a formula that gives me a running cumulative total of a material in one column based on the requirement qty in another column (EG Below). In Excel i can do this using the SUMIFS function but i do not know how to perform this in PowerQuery.

Thanks

[dufoq3 · Accepted solution]

Hi @James_White_199, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDLRNTAEIiDHUClWB03YCMgxBQs7oas2xxA2NAdyTDBVgwwxxjTbGMTBVA0SNlOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Material = _t, Date = _t, #"Req qty" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Req qty", type number}}),
    GroupedRows = Table.Group(ChangedType, {"Material"}, {{"All", each 
        [ a = Table.Sort(_, {{"Date", Order.Ascending}}),
          b = List.Buffer(a[Req qty]),
          c = List.Generate( ()=> [ x = 0, y = b{x}], each [x] < List.Count(b), each [ x = [x]+1, y = [y] + b{x} ], each [y] ),
          d = Table.FromColumns(Table.ToColumns(a) & {c}, Value.Type(a & #table(type table[Cumulative Total=number], {})))
        ][d], type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

[Ahmed_Kandil]

 hello , If you want to use sumifs in power query try selecting the column you want and go to

transform tab or add column-->group by--->then select operation sum 

hope this helps you