Context

PowerBI allows Left Anti joins (https://docs.microsoft.com/en-us/power-query/merge-queries-left-anti)

However, this function is not compatible with the "direct query" mode.

Here is a way to perform Left Anti joins using functions compatible with the "direct query" mode.


Resolution

In a nutshell

Use a LEFT JOIN and a calculated column.

In detail

In SQL, you want to achieve the following SQL : SELECT * FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.id = t2.id WHERE t2.id IS NULL.

Actions

In indexima:

  • For table2, create a calculated field that is the copy of the key used in the join, like this:

ALTER TABLE table2 ADD COLUMNS (id_copy as id);

In PowerBI:

  • Create a new column with the following formula: ISBLANK([id_copy]).
  • In the model, define a standard LEFT join between the 2 tables.
  • Use the new column as a filter on the report level and apply filters on the "empty" value.