Forum Discussion
Solved
Help with a formula
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
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
2 Replies
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
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
