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 the total value calculated within the group, with any remainder portioned into the final group (Plan-Type), with an example as below (please imagine this within PowerQuery):

In the data, individual Due totals are correct, but the Received total registers against the first entry. I am hoping to portion this total out over available Due figures within a particular grouping, resulting in the below:

I have looked into iteration and grouping but am hitting a block. Any ideas on how to achieve this using a dynamically changing dataset would be much appreciated!

Need Help

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"

4 Replies

Chewdata

Responsive Resident

2 years ago

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
Super 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
  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
ldoriejhl
Frequent Visitor
2 years ago
Thank you both for your solutions! Bhanu's has given me lots of points to examine and had lead me to research some of the methods described, and Chewdata's function has also proven to work.

Forum Discussion

Iterating over rows in table and portioning totals by group

4 Replies

Related content

Replacing values with Iterations

iterator

Cumulative Total

List.Accumulate & Table.AddColum with Iteration Step Counter

Iterator + related function