[Excel][Power Query] Reducing list of shipped goods by returned considering "first out first in"

My company is shipping some parts to external paintshops. There is a number of paintshops and many part numbers. I have a list with history of every move (shipped and returned). My goal is to process the list so only those recently sent goods are on the list. To explain it better, please see the example:

My list:

Supplier	Part No.	Date	Quantity	Type
S01	P0001	2024-05-05	-3	Received
S01	P0001	2024-05-04	2	Sent
S01	P0001	2024-05-03	-5	Received
S02	P0005	2024-04-24	-6	Received
S01	P0001	2024-04-02	3	Sent
S01	P0001	2024-03-11	10	Sent
S02	P0005	2024-03-05	4	Sent
S02	P0005	2024-02-05	3	Sent
S02	P0001	2024-01-25	-5	Received
S02	P0001	2024-01-14	5	Sent

Now, basing on this list, I want to keep those records of "Sent" type, which are still at supplier (didn't returned yet). And difficult part is, that not all sent quantity needs to be returned, which means that particular quantities need to be reduced by those already returned (result in separate column "Balance").

Expected result:

Supplier	Part No.	Date	Quantity	Balance
S01	P0001	2024-05-04	2	2
S01	P0001	2024-04-02	3	3
S01	P0001	2024-03-11	10	2
S02	P0005	2024-03-05	4	1

Explanation: The oldest shipped part P0001 to supplier S02 has been returned, hence two last record will be removed.

Part P0005 sent to supplier S02 in quantity 3 and then another 4, however 6 was returned later on. Considering "first out first in" rule, there are still 1 left at supplier S02, hence the 4th line in result table.

Consequently, part P0001 sent to supplier S01 (this has to be independend from those shipped to S02) in quantity of (10+3+2) and received painted (5+3). This returned quantity has to be deducted from first shipment, while other two shipments are still at supplier. Therefore in line 3 of result table the balance is (10-5-3=2) while in line 1 and 2 other two shipments will stay as they were.

My first attempt is to use Power Query, but I can't figure out how to do that. Is it even possible? If not, my next attempt will be VBA, but I really hope PQ can do that task.

Solution

Using Dict object to keep tracking the running total for each Supplier+Part No.

Option Explicit
Sub Demo()
    Dim oDicIn As Object
    Dim i As Long, j As Long, sKey As String
    Dim arrData, arrRes, iR As Long, ColCnt As Long
    Set oDicIn = CreateObject("scripting.dictionary")
    With Range("A1").CurrentRegion
        .Sort Key1:=.Columns(3), order1:=xlAscending, Header:=xlYes
        arrData = .Value
    End With
    arrRes = arrData
    ColCnt = UBound(arrData, 2)
    For i = LBound(arrData) To UBound(arrData)
        sKey = arrData(i, 1) & "|" & arrData(i, 2)
        If arrData(i, 5) = "Received" Then
            If oDicIn.exists(sKey) Then
                oDicIn(sKey) = oDicIn(sKey) + arrData(i, 4)
            Else
                oDicIn(sKey) = arrData(i, 4)
            End If
        End If
    Next i
    Dim iIn As Long, iOut As Long, bFlag As Boolean
    For i = LBound(arrData) To UBound(arrData)
        If arrData(i, 5) = "Sent" Then
            sKey = arrData(i, 1) & "|" & arrData(i, 2)
            bFlag = False
            If Not oDicIn.exists(sKey) Then
                bFlag = True
            Else
                iIn = oDicIn(sKey)
                iOut = arrData(i, 4)
                oDicIn(sKey) = IIf(iOut + iIn >= 0, 0, iOut + iIn)
                bFlag = (iIn + iOut > 0)
            End If
            If bFlag Then
                oDicIn(sKey) = iOut + iIn
                iR = iR + 1
                For j = 1 To ColCnt - 1
                    arrRes(iR, j) = arrData(i, j)
                Next
                arrRes(iR, j) = IIf(iIn <= 0, iOut + iIn, iOut)
            End If
        End If
    Next i
    Sheets.Add
    Range("A1:E1") = Array("Supplier", "Part No.", "Date", "Quantity", "Balance")
    Range("A2").Resize(iR, 5) = arrRes
End Sub