How to perform a left join based on two+ conditions

[oliverL]

Hi all,

I have been trying to merge two tables based on more than one condition without any successfull results. See, I have two tables with several fields but I am interested in only two of them. I would like to do a left join where one of the fields are the same, and the other contains the value from the first table. Once I have done this left join I would like to expand with several columns of table 2.

Let me explain it using an example:

Table 1 (Main table)

ID	Value	Extra
1	12	asd
2	34	asd
3	43	asd
4	452	asd
5	863	asd

Table 2 (Table with the extra information)

ID	Values	Wanted1	Wanted2
1	12, 45, 75	A	A
1		B	B
1	65, 86	C	C
2	41, 284, 37582, 57392	D	D
2	1, 43, 23, 34	E	E
87	684, 2, 5342	F	F

After performing the left join Table 1 would need to look as follows:

ID	Value	Extra	Wanted1	Wanted2
1	12	asd	A	A
2	34	asd	F	F
3	43	asd
4	452	asd
5	863	asd

Note how in Table 2 the ID can be duplicated and how the Values field can be blank. However, it won't happen that the combination of ID-Value is duplicated in Table 2. And even if that were to occur I would have no problem dealing with duplicated rows.

I would really appreciate the help.

[Anonymous · Accepted solution]

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."

[Icey · Accepted solution]

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.

[Anonymous]

I don't get the result you indicate, but maybe that's what you need.

[oliverL]

Hi @Anonymous ,

No, this is not the same scenario. In my case the field 'Values' from Table 2 contains several values. That's why I cannot use a simple NestedJoin. I need to perform the join whenever the IDs are the same and 'Value' from Table 1 is included in 'Values' from Table 2.

[Anonymous · Accepted solution]

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."