How to perform a left join based on two+ conditions

mmh ... my script starting from your example tables produces this output 

could you explain the logic for which ID = 2 and Value = 34 correspond to W1 and W2 equal to F and not to E as I found?

PS

I was able to use a simple join because I transformed the tables so that I could use it respecting the logic to be applied (the one I understood to be).

https://docs.microsoft.com/it-it/powerquery-m/table-nestedjoin

"

Joins the rows of table1 with the rows of table2 based on the equality of the values of the key columns selected by key1 (for table1) and key2 (for table2). The results are entered into the column named newColumnName.

The optional joinKind specifies the kind of join to perform. By default, a left outer join is performed if a joinKind is not specified.

An optional set of keyEqualityComparers may be included to specify how to compare the key columns. This feature is currently intended for internal use only."

Hi oliverL ,

Anonymous used functions to convert cell value into "List" and expand them. Then, merge the two tables and get what you want.

You can also split the "Values" column of Table2 by comma and expand to rows. It will give the same result.

Then, merge the two tables:

If you also need the orginal Values column in Table2, it is suggested to duplicate the Value column before split or just create a calculated column like so:

Column =
CONCATENATEX (
    FILTER (
        Table2,
        Table2[ID] = EARLIER ( Table2[ID] )
            && Table2[Wanted1] = EARLIER ( Table2[Wanted1] )
            && Table2[Wanted2] = EARLIER ( Table2[Wanted2] )
    ),
    [Values],
    ", "
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.