Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Handling cumulative measures in Power Query

Dear all,

 

so I have been following the following tutorial, however I came across a relatively simple problem. I perform my analysis in Excel, however that should not be a problem, with Power Query.

 

Consider the following table:

 

Table

We load it in into Power Query by pressing Data > From Table/ Range. Please name this table Table1.

We then add the measure to it, by selecting a cell in the table and then go to Power Pivot > Measures > New Measure...

With the new measure, we insert the following: 

=CALCULATE(SUM(Table1[Amount]), FILTER(Table1, Table1[GL]=EARLIER(Table1[GL])))

 

It should not matter what the measure name is nor the Table1 that we have selected. Anyways, we will get the following error message: "Calculation error in measure 'Table1'[measure 1]: EARLIER/EARLIEST refers to an earlier row context which doesn't exist.
".

 

I simply cannot comprehend why this method does not work and why the tutorial on the site does work. I am not sure what we do differently. I do understand the error and that is that the first row does not have a predecessor, however I am not sure how to solve this problem.

 

 

Any help or enlightenment would be highly appreciated.

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

4 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    The tutorial is using calculated columns. You are creating measures.  Either way is possible but the measure requires an aggregation in that filter.

    So if you're following the tutorial, stick to calculated columns

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Well... I tried that, too, but I get that CALCULATE was not detected in Excel. It seems that Power Query only allows for a M-formula and that the expression they have calculated there is a DAX and there is no way for me to turn it into an M-formula.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

       

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    I think your ultimate goal is to follow along with the tutorial and learn from it so you won't be creating a Power Query solution (yes, it's possible but you're not following the tutorial at that point).  You will create a calculated column in Excel and I know that it is possible.