cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oliverL
Frequent Visitor

How to perform a left join based on two+ conditions

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)

IDValueExtra
112asd
234asd
343asd
4452asd
5863asd

 

Table 2 (Table with the extra information)

IDValuesWanted1Wanted2
112, 45, 75AA
1 BB
165, 86CC
241, 284, 37582, 57392DD
21, 43, 23, 34EE
87684, 2, 5342FF

 

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

IDValueExtraWanted1Wanted2
112asdAA
234asdFF
343asd  
4452asd  
5863asd  

 

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.

2 ACCEPTED SOLUTIONS

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

 

image.png

 

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

 

image.png

View solution in original post

Icey
Community Support
Community Support

Hi @oliverL ,

 

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

list.gif

 

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

split.gif

 

Then, merge the two tables:

merge.gif

 

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.

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @oliverL ,

 

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

list.gif

 

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

split.gif

 

Then, merge the two tables:

merge.gif

 

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.

View solution in original post

Rocco_sprmnt21
Super User II
Super User II

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

Hi @Rocco_sprmnt21 ,

 

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.

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

 

image.png

 

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

 

image.png

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors