Handling cumulative measures in Power Query

Hi Anonymous ,

You may be a beginner to Power Query. The language written in the screenshot you provided is DAX. In Power Query, we use the M language.

If you want to group and accumulate in Power Query, you can do the following

1.Right click on the blank space and create an empty query.

2.Then in this empty query, click the advanced editor, copy the following formula in it, create a function, and rename the query to fn_cumulative_Total_en.

let
    Source = (Input as table) =>

let
    Sorting = Table.Sort(Input,{{"Date", Order.Ascending}}),                                        // Sort table
    added_Index = Table.AddIndexColumn(Sorting, "Index", 1, 1),                                      // add Index, base 1
    cumulativ_total = Table.AddColumn(                                                               // add new column with running total 
                                            added_Index, "Total", 
                                            each List.Sum(
                                                         List.Range(added_Index[Amount],0,[Index])
                                                        )
                                            ),
    extract_total = cumulativ_total[Total]                                                           // transform new column into list as result for grouping
in
extract_total
in
    Source

3.In the main table, your entire code is as follows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZCxCsAgDET/xdmAJkZ0LIX+hDh0697/hyoKRaNbuMedd6akzvt9lFZo0IAFW05rVNZLYHeAdsAJgICxEhaEgKoHgnzfAZl6R0G4mUhW883jGrmOsZmPSzms5XUG8yT3bTyH0FDy1/usOaZvCjjpfRGWT80f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [GL = _t, Date = _t, Amount = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"GL", type text}, {"Date", type date}, {"Amount", Int64.Type}}),
    Calc_Running_Total = Table.Group(
                                      #"Changed Type1",                                                                           // table as table
                                     {"GL"},                                                                       // key as any
                                                                                                                       // aggregatedColumns as list 
                                        {{"Data",                                                                           // name of new column to be created
                                    // Function Call
                                        (Input as table) as table =>                                                        // data for function
                                        let
                                        Call_Function = fn_cumulative_Total_en(Input),
                                    // End of Function Call

                                        result = Table.FromColumns(                                                         // putting all together
                                                                    Table.ToColumns(Input)&{Call_Function},
                                                                        Value.Type(
                                                                                   Table.AddColumn(
                                                                                                    Input, "Saldo", each null, type number
                                                                                                   )
                                                                                )
                                                                       )
                                        in
                                            result,


                                    type table}}                                                                    // transform into table
                                    ),
    #"Expanded Data" = Table.ExpandTableColumn(Calc_Running_Total, "Data", {"Date", "Amount", "Saldo"}, {"Date", "Amount", "Total"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"GL", type text}, {"Date", type date}, {"Amount", Int64.Type}, {"Total", Int64.Type}})
in
    #"Changed Type"

Reference: https://www.powerbi-pro.com/en/grouped-running-total-in-power-query/

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.