Forum Discussion
Lookup % Rebate on another table based on Invoice quantity
Hi,
I want to get the correct % Rebate from table A into table B based on the Invoice Qty of each Customer.
How can I get it done via power query?
Table A
Table B
12 Replies
NewStep=let a=Table.Buffer(Table.Group(TableA,"Key",{"n",Table.ToRows})) in Table.AddColumn(TableB,"%Rebate",each let lst=a{[Key=[Year_Chaneel]]}?[n]? in if lst=null then 0 else List.Last(List.RemoveLastN(lst,(x)=>x{1}>[Invoice Qty]),{}){2}? ??0)
Hi Daniel,
Thanks for your reply.
However based on your formula each Customer Code got duplicate into many lines with different %Rebate
NewStep=let a=Table.Buffer(Table.Group(TableA,"Key",{"n",each Table.ToRows(Table.Sort(_,"Min"))})) in Table.AddColumn(TableB,"%Rebate",each let lst=a{[Key=[Year_Chaneel]]}?[n]? in if lst=null then 0 else List.Last(List.RemoveLastN(lst,(x)=>x{1}>[Invoice Qty]),{}){2}? ??0)
try this
Custom1 = Table.AddColumn( Data, "Rebate", each List.Select( Table.ToRows( Table.ReverseRows( Rebate ) ), (x)=> x{0} = [Year Channel] and x{1} <= [Invoice Qty] ){0}?{2}? ?? 0 )Hi Kirete,
Still get the same result, duplicating the records for each customer code with different %Rebate
