I have many derived tables in Power BI and I have a table having a list of ids.
Derived tables are like this –
Table 1 –
| IDs | Value | Address |
|---|---|---|
| A1 | Two | US – 50878 |
| A2 | Three | US – 50879 |
| A3 | Four | US – 50888 |
| A4 | Five | US – 50898 |
Table 2 –
| IDs | Value | Address |
|---|---|---|
| A3 | Two | US – 50878 |
| A4 | Three | US – 50879 |
| A5 | Four | US – 50888 |
| A6 | Five | US – 50898 |
Like that, I have many tables in Power BI.
I have an exception table having a list of ids and my requirement is to filter out the above-derived table based on the ids available in the exception table.
Like my exception table is –
| ids |
|---|
| A3 |
| A4 |
Since A3 and A4 are in the exception table then Table 1 and Table 2 should not contain these 2 ids rows.
Expected results should be
Table 1 –
| IDs | Value | Address |
|---|---|---|
| A1 | Two | US – 50878 |
| A2 | Three | US – 50879 |
Table 2 –
| IDs | Value | Address |
|---|---|---|
| A5 | Four | US – 50888 |
| A6 | Five | US – 50898 |
>Solution :
In Transform Data, do a left anti join merge between each table and your exceptions.
Before:
After:
