Amount Adjustment on FIFO basis

[peterpan]

I have two tables. Table A-

Invoice Number	Amount
4001	50000
4002	60000
4003	95000
4002	2000

Table B-

Invoice Number	Invoice Item	Invoice Amount
4001	18	30000
4001	19	20000
4002	22	26000
4002	23	38000
4002	25	20000
4003	36	80000
4003	38	5000

I need to add a third column to adjust the amount paid against invoice item amount on FIFO basis as below. Need your assistance-

Invoice Number	Invoice Item	Invoice Amount	Amount Paid
4001	18	30000	30000
4001	19	20000	20000
4002	22	26000	26000
4002	23	38000	36000
4002	25	20000	0
4003	36	80000	80000
4003	38	5000	5000

[Fowmy]

@peterpan 

I know you have posted this question under Power Query but I would like to see if you are interested in a DAX solution, if so, add a calculated column in Table B, using the code below :

Amount Paid = 
VAR __InvNum = 'Table B'[Invoice Number]
VAR __InvItem = 'Table B'[Invoice Item]
VAR __InvAmount = 'Table B'[Invoice Amount]
VAR __TotalPayment = SUMX( FILTER( 'Table A' , 'Table A'[Invoice Number] = __InvNum ) , 'Table A'[Amount] )
VAR __CumAmount =  SUMX( FILTER( 'Table B' , 'Table B'[Invoice Number] = __InvNum && 'Table B'[Invoice Item] <= __InvItem ) , 'Table B'[Invoice Amount] )
VAR __RemAmount =  __TotalPayment - __CumAmount
RETURN
   IF( __RemAmount >= 0 , __InvAmount  , MAX( 0,  __InvAmount +  __RemAmount )  )

Output:

[AlienSx]

@peterpan 

let
    payments = your_TableA,
    items = your_TableB,
    f = (tbl) => 
        [recs = Table.ToRecords(tbl),
        payment = Record.FieldOrDefault(pr, Text.From(tbl[Invoice Number]{0}), 0),
        gn = List.Generate(
            () => 
                [i = 0, 
                paid = List.Min({recs{0}[Invoice Amount], payment}), 
                balance = List.Max({0, payment - paid}), 
                t = recs{0} & [Amount Paid = paid]],
            (x) => x[i] < List.Count(recs),
            (x) => 
                [i = x[i] + 1, 
                paid = List.Min({recs{i}[Invoice Amount], x[balance]}), 
                balance = List.Max({0, x[balance] - paid}), 
                t = recs{i} & [Amount Paid = paid]],
            (x) => x[t]
        ),
        back_to_tbl = Table.FromRecords(gn)][back_to_tbl],
    pg = Table.Group(payments, "Invoice Number", {"p", each List.Sum(_[Amount])}),
    pr = Record.FromList(pg[p], List.Transform(pg[Invoice Number], Text.From)),
    pi = Table.Group(items, "Invoice Number", {"pi", each f(Table.Sort(_, "Invoice Item"))}),
    expand = Table.ExpandTableColumn(pi, "pi", {"Invoice Item", "Invoice Amount", "Amount Paid"})
in
    expand