Forum Discussion

ldoriejhl's avatar
ldoriejhl
Frequent Visitor
2 years ago
Solved

Iterating over rows in table and portioning totals by group

Hi, Hopefully someone can help me with a particular task; I need to iterate over a table sequentially and recalculate a column (labelled "Received") based on both the value in the "Due" column and...
Need Help
  • Chewdata's avatar
    Chewdata
    2 years ago

    Hey,

    I've used a function to solve this. 
    The function creates to total received and running total for due.
    It then wil create a new column for receiverd.
    Based on a index it wil then check if de record is the last record of the particular plan. if so it wil calculate the remainder.


    Please try this code:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCshJzDPUDaksSDVU0lEyNDCAkwZKsTrI8kZAMSOwjAVOeWOoDHZZE7gsyAaYvBGa7UZopqPLY5M1gstaQIyAyxuj6TZBMx1dHpesKT5JhFZdoLrYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Plans = _t, Due = _t, Received = _t, #"Difference (wanted)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Plans", type text}, {"Due", Int64.Type}, {"Received", Int64.Type}, {"Difference (wanted)", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Plans"}, {{"Table", each _, type table [Plans=nullable text, Due=nullable number, Received=nullable number, #"Difference (wanted)"=nullable number]}}),
    
    // function
    fnRunningtotal = (vTable as table) =>
    let
        add_index = Table.AddIndexColumn(vTable, "Index", 1, 1, Int64.Type),
        add_DueTotal = Table.AddColumn(add_index, "DueTotal", each List.Sum(List.Range(add_index[Due], 0, [Index]))),
        add_ReceivedTotal = Table.AddColumn(add_DueTotal, "ReceivedTotal", each List.Sum(add_DueTotal[Received])),
        add_Received = Table.AddColumn(add_ReceivedTotal, "ReceivedNew", each if [Index] = List.Max(add_ReceivedTotal[Index]) then  [Due] + ([ReceivedTotal] - [DueTotal]) else if [ReceivedTotal] >= [DueTotal] then [Due] else [DueTotal] - [ReceivedTotal], Int64.Type),
        add_Difference = Table.AddColumn(add_Received, "Difference", each if [Index] = List.Max(add_Received[Index]) then [DueTotal] - [ReceivedTotal] else 0, Int64.Type) 
    in
        add_Difference,

    inv_fnRunningtotal = Table.TransformColumns(#"Grouped Rows", {"Table", fnRunningtotal}),
    #"Expanded Table" = Table.ExpandTableColumn(inv_fnRunningtotal, "Table", {"Plans", "Due", "ReceivedNew", "Difference"}, {"Plans.1", "Due", "Received", "Difference"})

in
    #"Expanded Table"

     

     

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

ldoriejhl , Try using below steps

 

Go to the "Home" tab and click on "Group By".
In the "Group By" dialog, select "Plan-Type" as the column to group by.
Add an aggregation for the "Due" column to get the total "Due" for each group.
Add Custom Column for "Received" Calculation:

After grouping, add a custom column to calculate the "Received" value.
Use the following M code to create the custom column:

m
let
Source = YourDataSource,
GroupedData = Table.Group(Source, {"Plan-Type"}, {{"AllData", each _, type table [Plan-Type=nullable text, Due=nullable number, Received=nullable number]}}),
AddReceivedColumn = Table.TransformColumns(GroupedData, {"AllData", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}),
CalculateReceived = Table.TransformColumns(AddReceivedColumn, {"AllData", each
let
TotalDue = List.Sum([Due]),
TotalReceived = List.Sum([Received]),
Calculate = List.Accumulate([Due], [TotalReceived, 0], (state, current) =>
let
remaining = state{0} - state{1},
received = if remaining > current then current else remaining,
newState = {state{0}, state{1} + received}
in
newState
)
in
Table.FromColumns(Table.ToColumns(_) & {List.Transform(Calculate, each _{1})}, Table.ColumnNames(_) & {"CalculatedReceived"})
})
in
CalculateReceived

 

After calculating the "Received" values, expand the grouped data to get the final table.
Click on the expand icon next to the "AllData" column and select the columns you want to include in the final table.

ldoriejhl's avatar
ldoriejhl
Frequent Visitor
2 years ago

Thanks for your quick response Bhanu!

I'm just working through this sequentially as loading this into a new step at once is returning some issues

  • I've grouped the Plan-Type and created a Sum column based on Due, as well as an All Rows operation column named "AllData"
  • I have then added an index column to the newly grouped data
  • I'm now attempted to add in a Custom Column using the following:
    • let
      TotalDue = List.Sum([Due]),
      TotalReceived = List.Sum([Received]),
      Calculate = List.Accumulate([Due], [TotalReceived], 0, (state, current) =>
      let
      remaining = state{0} - state{1},
      received = if remaining > current then current else remaining,
      newState = {state{0}, state{1} + received}
      in
      newState
      )
      in
      Table.FromColumns(Table.ToColumns(_) & {List.Transform(Calculate, each _{1})}, Table.ColumnNames(_) & {"CalculatedReceived"})

This last step returns an error on the new column (We cannot convert a value of type Record to type Table: Value = [Record], Type=[Type]), so I'm unsure which part I've missed