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.
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
Solved! Go to 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]
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
@v-haibl-msft Thanks for help, but i am looking for Power Query solution on this.
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}
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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]
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF, as I understnad it is recommended to go for "student table (many) to lookup values from the Result-table (one)".
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |