Dear Power Bi fans and developers,
I would like to create completely common accountant procedure that distributes one fixed initial amount of payment received (credit) between individual invoices (debit), from oldest invoice, each up to amount due (and transfering the remaining value to be distribued in next rows).
This procedure is simple in Excel tables and Excel formulas (XL), eg. as follows (amount to distribute is 2500):
But I worry about such a setup in PowerQuery (PQ, let's not deal with DAX please).
My idea was that I would simply create the same procedure/custom columns in PQ as above in the XL example using the column index n, so:
- payment{n} = List.Min(credit{n-1}, debit{n})
- credit{n} = credit{n-1} - payment{n}
Scheme for the source and the result would be cca:
But I quickly found that PQ thinks differently than XL - e.g. creates new columns one by one - so I can't use Table.AddColumn at all because both target data refer to just the opposite column (function for column payment needs value in column credit and vice versa).
I tried to secure that I already have value of credit{n-1} in the moment of creating payment{n} and value of payment{n} for creating credit{n}. So I create column pair of type list for pair such {payment, credit} - in each row a list with two elements. But I also encountered a problem with the sequence. I may refer to pair{n-1} but it will be searched for in a table that is in a state before {n-1} was created by this procedure, so it is not accumulating.
In other words, probably the goal is to find a procedure that always creates a new row (or some values of such row record) from the previous row, and so on for each row until row count of source table.
I studied the well-known user defined procedure for RunningTotals via List.Sum(Table.SelectRows(tab, each [index] <= index)[column]) and it has the following scheme:
I studied the procedure for List.Accumulate and it has the following scheme:
I'm afraid that none of this is inherent to the scheme I need.
But I very much believe that the solution have to be found and that it will be built mostly on List.Accumulate, but I can't figure out how.
Thank you in advance
Marek
I would like to create completely common accountant procedure that distributes one fixed initial amount of payment received (credit) between individual invoices (debit), from oldest invoice, each up to amount due (and transfering the remaining value to be distribued in next rows).
This procedure is simple in Excel tables and Excel formulas (XL), eg. as follows (amount to distribute is 2500):
But I worry about such a setup in PowerQuery (PQ, let's not deal with DAX please).
My idea was that I would simply create the same procedure/custom columns in PQ as above in the XL example using the column index n, so:
- payment{n} = List.Min(credit{n-1}, debit{n})
- credit{n} = credit{n-1} - payment{n}
Scheme for the source and the result would be cca:
But I quickly found that PQ thinks differently than XL - e.g. creates new columns one by one - so I can't use Table.AddColumn at all because both target data refer to just the opposite column (function for column payment needs value in column credit and vice versa).
I tried to secure that I already have value of credit{n-1} in the moment of creating payment{n} and value of payment{n} for creating credit{n}. So I create column pair of type list for pair such {payment, credit} - in each row a list with two elements. But I also encountered a problem with the sequence. I may refer to pair{n-1} but it will be searched for in a table that is in a state before {n-1} was created by this procedure, so it is not accumulating.
In other words, probably the goal is to find a procedure that always creates a new row (or some values of such row record) from the previous row, and so on for each row until row count of source table.
I studied the well-known user defined procedure for RunningTotals via List.Sum(Table.SelectRows(tab, each [index] <= index)[column]) and it has the following scheme:
I studied the procedure for List.Accumulate and it has the following scheme:
I'm afraid that none of this is inherent to the scheme I need.
But I very much believe that the solution have to be found and that it will be built mostly on List.Accumulate, but I can't figure out how.
Thank you in advance
Marek
