Skip to main content
Power BI Interview Questions

You have two tables, A and B, with the same structure. You want to create a third table, C, which contains the values in A that are not in B?

Table of Contents

What are the two most common ways to create a table in Power BI that contains the values in Table A that are not in Table B?

Can you explain the difference between the left anti-join and the full outer merge in Power Query, and which one you would use to create a table in Power BI that contains the values in Table A that are not in Table B?

In Power Query, you can do this by Left Anti joining in Merged Queries. First select the primary key column (or columns, if the need be, select multiple columns by holding the Ctrl key, clicking in the same order for both tables). Then apply Left Anti to get the Unique records for Table A.

  1. Under the Home tab, click Transform Data to open the Power Query Editor.
  2. Select Table A.
  3. In the Combine group on the Home tab, click Merge Queries. You will be presented with two options: Merge Queries and Merge Queries as New.
  4. Select Merge Queries as New.
  5. In the Merge Queries dialog box, choose Table B.
  6. Select the columns from both tables that you want to merge. To select multiple columns, hold Ctrl and click the columns.
  7. Choose Left Anti Join as the join type.
  8. Click OK.
  9. Rename the resulting table/query as C.

To create the table you want in DAX, use EXCEPT. The Power BI EXCEPT function returns the rows from the first of the two tables passed to it that are not in the second table.

  • Select either Table A or Table B.
  • On the Table Tools tab, click New Table.
  • Use the following DAX expression:
    C = EXCEPT(A, B)

Add comment