Forum Discussion

James_White_199's avatar
James_White_199
Frequent Visitor
2 years ago
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...
  • dufoq3's avatar
    2 years ago

    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