Forum Discussion

k33fun's avatar
k33fun
Frequent Visitor
3 years ago

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    
Need Help
k33fun's avatar
k33fun
Frequent Visitor
3 years ago

Hi Daniel,

 

Thanks for your reply.

However based on your formula each Customer Code got duplicate into many lines with different %Rebate

 

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago

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)

  • k33fun's avatar
    k33fun
    Frequent Visitor
    3 years ago

    Hi Daniel,

     

    Table A = CTC_Rule
    Table B = Cognos_Total_Pivot 

     

    Below error message appear

     

     

    • wdx223_Daniel's avatar
      wdx223_Daniel
      Community Champion
      3 years ago

       

      try to delete those code

      • k33fun's avatar
        k33fun
        Frequent Visitor
        3 years ago

        origin is like this but after save it show me that error message