Power Query Running_On_hand

[MirageZX]

Hi, I am new in Power Query and I have been struggling to find out how to create a Column name "Running_On_hand". The source data are two tables: 

Material	Res_Date	Reservation_Number	Req_Qty
10292891	7/3/2021	563231	1
10292891	10/10/2022	2365056	1
10023987	7/3/2021	562539	1
10272299	7/3/2021	555273	3
10272299	12/24/2022	2701112	8
10272299	2/13/2023	2944045	10
10272299	2/16/2023	2959099	1
10272299	3/2/2023	3021208	5
10272299	3/9/2023	3050715	30
10272299	3/23/2023	3117994	5
10272299	4/3/2023	3164770	5
10195249	7/8/2021	567184	1
10195249	7/8/2021	567912	1
11003980	7/8/2021	558134	750

Table1

Material	On_Hand_Qty
10292891	2
10023987	1
10272299	1
10195249	2
11003980	1500

Table2

The goal is to obtain a new Column that refer to the On_hand_Qty from Table2 as initial and go subtracting the Req_Qty for each material. 

Material	Re_Creation_D	Reservation_Number	Req_Qty	Running_Stock
10292891	7/3/2021	563231	1	1
10292891	10/10/2022	2365056	1	0
10023987	7/3/2021	562539	1	0
10272299	7/3/2021	555273	3	-2
10272299	12/24/2022	2701112	8	-10
10272299	2/13/2023	2944045	10	-20
10272299	2/16/2023	2959099	1	-21
10272299	3/2/2023	3021208	5	-26
10272299	3/9/2023	3050715	30	-56
10272299	3/23/2023	3117994	5	-61
10272299	4/3/2023	3164770	5	-66
10195249	7/8/2021	567184	1	1
10195249	7/8/2021	567912	1	0
11003980	7/8/2021	558134	750	750

New Column demonstration

Any help will be highly appreciated.

PD: I don't know how to make the table looks better in the post; the style or the width of each column

[AlienSx · Accepted solution]

Hello, @MirageZX 

let
    t1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    t2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    lookup_t = Record.FromList(t2[On_Hand_Qty], List.Transform(t2[Material], Text.From)),
    f = (t as table) => 
        [opening_balance = Record.Field(lookup_t, Text.From(t[Material]{0})),
        pos = List.Buffer(List.Skip(List.Positions(t[Material]))),
        res = List.Buffer(t[Req_Qty]),
        balances = 
            List.Accumulate(
                pos,
                {opening_balance - res{0}},
                (s, c) => s & { s{c - 1} - res{c} }
            ),
        out = Table.FromColumns(Table.ToColumns(t) & {balances}, Table.ColumnNames(t) & {"Running_Stock"})][out],
    gr = Table.Group(t1, {"Material"}, {{"all", each f(Table.Sort(_, "Res_Date"))}}),
    z = Table.Combine(gr[all])
in
    z

[MirageZX]

Thanks mate! It works!!

[ppm1]

This is doable, but why not just do it with a DAX measure (loading both tables into your model).

Pat

[MirageZX]

Could you please provide the DAX code needed (Power Pivot) or the Power Query alternative?