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
wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago

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)

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