Lookup % Rebate on another table based on Invoice quantity

[k33fun]

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

[kirete17]

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
        )

[k33fun]

Hi Kirete,

Still get the same result, duplicating the records for each customer code with different %Rebate

[wdx223_Daniel]

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]

Hi Daniel,

Thanks for your reply.

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

[wdx223_Daniel]

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]

Hi Daniel,

Table A = CTC_Rule
Table B = Cognos_Total_Pivot 

Below error message appear

[wdx223_Daniel]

try to delete those code

[k33fun]

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

[wdx223_Daniel]

no need open this windows, jut put those code in the editor pane which is after "fx" lable

[k33fun]

OK i got it but still appear the same error

[wdx223_Daniel]

what's the relationship between those two tables? does one table comes from the other?

[k33fun]

It is a many to many relationship between these 2 tables applied.