cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## 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 12
Frequent Visitor

try this

``````Custom1 =
Data,
"Rebate",
each
List.Select(
Table.ToRows( Table.ReverseRows( Rebate ) ),
(x)=> x{0} = [Year Channel] and x{1} <= [Invoice Qty]
){0}?{2}? ?? 0
)``````
Frequent Visitor

Hi Kirete,

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

Super User

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)

Frequent Visitor

Hi Daniel,

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

Super User

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)

Frequent Visitor

Hi Daniel,

Table A = CTC_Rule
Table B = Cognos_Total_Pivot

Below error message appear

Super User

try to delete those code

Frequent Visitor

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

Super User

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

Frequent Visitor

OK i got it but still appear the same error

Super User

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

Frequent Visitor

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors