Forum Discussion

MirageZX's avatar
MirageZX
Frequent Visitor
3 years ago
Solved

Power Query Running_On_hand

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: 

 

MaterialRes_DateReservation_NumberReq_Qty
102928917/3/20215632311
1029289110/10/202223650561
100239877/3/20215625391
102722997/3/20215552733
1027229912/24/202227011128
102722992/13/2023294404510
102722992/16/202329590991
102722993/2/202330212085
102722993/9/2023305071530
102722993/23/202331179945
102722994/3/202331647705
101952497/8/20215671841
101952497/8/20215679121
110039807/8/2021558134750

Table1

 

MaterialOn_Hand_Qty
102928912
100239871
102722991
101952492
110039801500

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. 

 

MaterialRe_Creation_DReservation_NumberReq_QtyRunning_Stock
102928917/3/202156323111
1029289110/10/2022236505610
100239877/3/202156253910
102722997/3/20215552733-2
1027229912/24/202227011128-10
102722992/13/2023294404510-20
102722992/16/202329590991-21
102722993/2/202330212085-26
102722993/9/2023305071530-56
102722993/23/202331179945-61
102722994/3/202331647705-66
101952497/8/202156718411
101952497/8/202156791210
110039807/8/2021558134750750

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

Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    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

4 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

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

     

    Pat

    • MirageZX's avatar
      MirageZX
      Frequent Visitor
      3 years ago

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

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    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