Unable to merge or join tables based on condition

[amshukla]

I have Student table such as:

Student	Marks
A	35
B	25
C	95
D	65

I have another table such as Result:

Min_marks	Max_marks	Result
0	30	Fail
31	50	Average
51	75	Good
76	100	Excellent

How to merge or Join above two tables to get output as below table(there should be relation between them):

Student	Marks	Result
A	35	Average
B	25	Fail
C	95	Excellent
D	65	Good

This can be done easily in qlikveiw using IntervalMatch() function but not sure how to do it PowerBI.

Regards,

Amit

[v-haibl-msft]

@amshukla

You can also try to use following DAX forumual to create a calculated column.

Column = 
CALCULATE (
    MAX ( Result[Result] ),
    FILTER (
        Result,
        Student[Marks] >= Result[Min_marks]
            && Student[Marks] <= Result[Max_marks]
    )
)

Best Regards,
Herbert

[amshukla]

@v-haibl-msft Thanks for help, but i am looking for Power Query solution on this.

[ImkeF]

If you add a column to table "Student" with the following formula it should work:

Table.SelectRows(Result, (Result) => Result[Min_marks]<=[Marks] and Result[Max_marks] >= [Marks])[Result]{0}

[amshukla]

Hi @ImkeF,

Thanks for the response.

I wanted to create a new column in Result table using your formula i.e

Table.SelectRows(Student, (Student) => Student[Marks]>=[Min_marks] and Student[Marks]<=[Max_marks])[Student]{0}

and it is working but when I added new rows & column in student table(refer below table) it is not giving the desired output.

Student Table:

Student	Marks	Address
A	35	q
B	25	p
C	95	r
D	65	s
E	10	t
F	45	v

and the output which i am getting is:

Output:

Min_marks	Max_marks	Result	Custom
0	30	Fail	B
31	50	Average	A
51	75	Good	D
76	100	Excellent	C

Desired Output is :

Min_marks	Max_marks	Result	Student	Address
0	30	Fail	B	p
31	50	Average	A	q
51	75	Good	D	s
76	100	Excellent	C	r
0	30	Fail	E	t
31	50	Average	F	v

Please let me know where I am missing

Regards,

Amit

[ImkeF · Accepted solution]

Hi @amshukla,

you added the column to the wrong table. The formula I gave you was intended to be used in the student table (many) to lookup values from the Result-table (one). You then expand that newly generated column and select the columns from the lookup-table that you need.

What you did was to re-write the formula so it would look up many values from the student table to one row of your lookup-table.

That is  unusual and would make your lookup-table unusable for any further use (as lookup table).

You can make it work by just omitting the last {0} like so:

Table.SelectRows(Student, (Student) => Student[Marks]>=[Min_marks] and Student[Marks]<=[Max_marks])[Student]

[amshukla]

@ImkeF, as I understnad it is recommended to go for "student table (many) to lookup values from the Result-table (one)".