Handling of Duplicate Datasets in tables

Hi all

Here a question about cleaning a dataset.

I have two tables. Data and Dictionary.

My target is a result table with a 1:1 match of data and dictionary values.

The total amount of data in both tables is the same.

I use VALUE LOOKUP node - so far, so easy.

The problem is, that there are occasionally values in each table (in the lookup column) that have the same value - so I get multiple matches with the dictionary table, n:1 and therefore also data in the dictionary table that is not matched at all to the data table.

I have a way to filter the orphans - by stacking Value Lookup nodes behind each other.

I also have a “clean” result table without the duplicates - using the Duplicate Row Filter node.

Now comes the tricky part:

How do I get the orphan datasets back into the results, with the match of the not-yet-matched dictionary?

Everything I try just keeps getting me the again the duplicate results, and not matches to the not-yet-matched data.

I feel like I am in a catch 22. Grateful for any ideas…