cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amshukla Frequent Visitor
Frequent Visitor

Unable to merge or join tables based on condition

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Unable to merge or join tables based on condition

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]

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




6 REPLIES 6
Super User
Super User

Re: Unable to merge or join tables based on condition

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}

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




v-haibl-msft Super Contributor
Super Contributor

Re: Unable to merge or join tables based on condition

@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]
    )
)

Unable to merge or join tables based on condition_1.jpg

 

Best Regards,
Herbert

amshukla Frequent Visitor
Frequent Visitor

Re: Unable to merge or join tables based on condition

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

amshukla Frequent Visitor
Frequent Visitor

Re: Unable to merge or join tables based on condition

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:

StudentMarksAddress
A35q
B25p
C95r
D65s
E10t
F45v

 

and the output which i am getting is:

 

Output:

Min_marksMax_marksResultCustom
030FailB
3150AverageA
5175GoodD
76100ExcellentC

 

Desired Output is :

 

Min_marksMax_marksResultStudentAddress
030FailBp
3150AverageAq
5175GoodDs
76100ExcellentCr
030FailEt
3150AverageFv

 

Please let me know where I am missing

Regards,

Amit

Super User
Super User

Re: Unable to merge or join tables based on condition

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]

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Highlighted
amshukla Frequent Visitor
Frequent Visitor

Re: Unable to merge or join tables based on condition

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