Forum Discussion
Helper VPayment Allocations via PQ
Hello all,
I have two departments in my business where let's say I sell in one department services and in the other department I sell goods. I can have customers who will buy services only, goods only or both. My goal is to create a table where I will see all what I sell (services and goods) and all the payments from my customers who will be allocated automatically. I would like to achieve this goal using Power Query
I have attached 3 pictures : my table where I do record the prodcuts sold (goods and services, I called them Type 1 and Type 2), another table showing the payments the way I record them and finally a pic showing the expected result
Hope it makes sense to all of you and thanks for your time and your help
Vijay_A_Verma, wdx223_Daniel, m_dekorte, ronrsnfld, olgad tblInvoicestblPaymentsExpected Result
let
Invoice=tblInvoices,
Payment=tblPayment,
Custom1=let a=Table.Buffer(Table.Group(Payment,"Customer",{"n",each List.Sum([Payment])})) in Table.FromRecords(List.Accumulate(Table.ToRecords(Invoice),{{},[]},(x,y)=>let b=Record.FieldOrDefault(x{1},y[Customer],a{[Customer=y[Customer]]}?[n]?)??0,c=List.Min({y[Invoice],b},d=List.Max({0,y[Invoice]-c}) in {x{0}&{y&[Allocated=c,#"O/S Bal"=y[Invoice]-c]},x{1}&Record.AddField([],y[Customer],b-c)}){0})
in
Custom1
8 Replies
- wdx223_Daniel
Community Champion
let
Invoice=tblInvoices,
Payment=tblPayment,
Custom1=let a=Table.Buffer(Table.Group(Payment,"Customer",{"n",each List.Sum([Payment])})) in Table.FromRecords(List.Accumulate(Table.ToRecords(Invoice),{{},[]},(x,y)=>let b=Record.FieldOrDefault(x{1},y[Customer],a{[Customer=y[Customer]]}?[n]?)??0,c=List.Min({y[Invoice],b},d=List.Max({0,y[Invoice]-c}) in {x{0}&{y&[Allocated=c,#"O/S Bal"=y[Invoice]-c]},x{1}&Record.AddField([],y[Customer],b-c)}){0})
in
Custom1
EinomiI really appreciate your help. I would like to give it a try.
However, I believe there is a comma missing somewhere, could you help me ? It will me take some time to understand your code
- Einomi
There was a ')' missing. Figured out, it seems to work perfectly, many thanks
- Einomi
Again, thanks
I just gave it a try in my real dataset, what if a customer sends in more money than he actually owes. With the code you provided, the column O/S bal will show 0 (this is what I asked, thanks).
Do you have an idea how we can display credit in the O/S bal column ?
- wdx223_Daniel
it depends on what you defind the "O/S Bal", this code will show the O/S balance accumulatedly
let
Invoice=tblInvoices,
Payment=tblPayment,
Custom1=let a=Table.Buffer(Table.Group(Payment,"Customer",{"n",each List.Sum([Payment])})) in Table.FromRecords(List.Accumulate(Table.ToRecords(Invoice),{{},[]},(x,y)=>let b=Record.FieldOrDefault(x{1},y[Customer],a{[Customer=y[Customer]]}?[n]?)??0,c=List.Max({0,List.Min({y[Invoice],b})}) in {x{0}&{y&[Allocated=c,#"O/S Bal"=b-y[Invoice]]},x{1}&Record.AddField([],y[Customer],b-y[Invoice])}){0})
in
Custom1