Conditional column based on if a record exists in another table

[WBADAM03]

Hey all,

I hope this is the right place to ask this question:

I have table1 that has a list of students.

I have table2 that has a table with student records.

I want to have a column in table1 that looks at table2 and, if a record/row exists in table2 with the username of the student, returns a specific column. If there is no record at all in table2 for the student based on their username, I want it to return null or NA.

I am not great at powerbi so I am having trouble.

I tried conditional column but it won't let me do a conditional based on the column of another table.

So if I have a student named Bob, if Bob's username is student3 (the key between the tables), if student3 is in table2 and the data value is "approved", I want newcolumn to return "approved".

If John's username is student5 and if student5 doesn't exist in table2, I want newcolumn to have a value of "NA" or null for his record in table1.

Does that make sense? 

Been having some trouble with this one

[Anonymous · Accepted solution]

Hi @WBADAM03 ，

I created some dummy data for reference.

Table (1) has the student's names, Table (2) has the students' grades, and if the student in Table (1) has no grades in Table (2), then NA is returned.

Then you can create the following column to solve it.

Column = 
IF(
    'Table (1)'[Student] in SELECTCOLUMNS('Table (2)',"Student",[Student]),"Y","NA")

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[AlienSx]

table2_rec = Record.FromList(table2[data_value], table2[key]),
find_key = Table.AddColumn(table1, "column", each Record.FieldOrDefault(table2_rec, [key], "NA"))

[WBADAM03]

Hi, 

I am not sure I understand the question. 

Basically I want a column in table1 that looks at table2 to see if a record exists for the student using the student username (key that joins the table). If a record does exist in table2 for the student, I want to return an advisor record, like Bob Jones.

If I have a student named Dave Smith and he has a record in table2 with the username "student1", I want to return his advisor, Bob Jones, as a value in a column in table1.

If Dave Smith doesn't have a record in table2 with the username student1, I want to return null or NA

Does that make sense?

[mussaenda]

Hi @WBADAM03 

Can you please provide a data that users can copy and your expected output to help you better?