Skip to main content
Skip table of contents

Left.Anti

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.


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.