Filter table with exception

[lylx]

I have problem in Power bi:

I have 2 table:

  Table1                                               Table2

name      class                                    name      class

John        A1                                      John        A1

John        A2                                      John        A2

John        A3                                      Mary       A3

Mary       A1                                      Mary       A1

Mary       A2                                      Arya         A2

Mary       A3                                      Arya         A1

Arya        A1                                      Arya         A1

Arya        A2                                      Mary       A1

Arya        A3   

I want to result: ( (name, class) from Table 1 which does not exist in Table 2)

name      class 

John        A3

Mary       A2

Arya        A3       

get me some advice.

[Zubair_Muhammad · Accepted solution]

@lylx 

You can merge the 2 queries and then select empty Tables resulting from the merge

Please see attached file's Query Editor for steps (with your sample data)

[Zubair_Muhammad · Accepted solution]

@lylx 

Also you can create a Calculated Table with DAX from the Modelling Tab>>new Table

If you have just 2 columns in both tables, then

Calculated Table = Except(Table1,Table2)

[lylx]

thank you so much!

[Zubair_Muhammad · Accepted solution]

@lylx 

You can merge the 2 queries and then select empty Tables resulting from the merge

Please see attached file's Query Editor for steps (with your sample data)