How to mimic SQL "Full Outer Join" in M?

[vonschultz666]

Hi everyone!

I have one challenge I would like to resolve with Power Query, and hopefully, someone will be able to help.

Currently, I have two views on two different MS SQL servers. The first view uses local authentication, while the second view uses AD authentication. The result I would like to achieve is written in SQL as follows:

SELECT *
FROM [A]
FULL OUTER JOIN [B] ON A.No_=B.SupplierArticleID AND B.Supplier='Firm XXX'

I want to get the following table:

	B.SupplierArticleID	B.Supplier='Firm XXX'	B.No_
Row Result 1.	10000	Yes	10000
Row Result 2.	10000	No	null
Row Result 3.	10001	No	null
Row Result 4.	null	null	10002

I've created the following M Power Query:

let
    // Add prefix to column names in B and A
    RenameBColumns = Table.TransformColumnNames(B, each "B." & _),
    RenameAColumns = Table.TransformColumnNames(A, each "A." & _),

    // Filter B table for Supplier = 'Firm XXX'
    FilteredB = Table.SelectRows(RenameBColumns, each [B.Supplier] = "Firm XXX"),

    // Perform left outer join 
    LeftJoin = Table.Join(RenameAColumns, "A.No_", FilteredB, "B.ArticleSupplierID", JoinKind.LeftOuter),
		
    // Perform Left Anti Join on A table to get the rows which are not included in LeftJoin
    LeftAntiJoinA = Table.Join(RenameAColumns, "A.No_", LeftJoin, "A.No_", JoinKind.LeftAnti),

    // Perform Right Anti Join on original B table to get the rows which are not included in LeftJoin
    RightAntiJoinB = Table.Join(LeftJoin, "B.ArticleSupplierID", RenameBARColumns, "B.ArticleSupplierID", JoinKind.RightAnti),

    // Combine all tables to mimic full outer join
    FullOuterJoin = Table.Combine({LeftJoin, LeftAntiJoinA, RightAntiJoinB})
in
    FullOuterJoin

The issue arises because when using the LeftJoin, we can cover Row Result 1. With AntiJoins, we can cover Row Results 3. and 4., but Row Result 2. would not be covered. This means that the table RightAntiJoinB might not contain the SupplierArticleID if there are two rows with the same value. Specifically, one row with Supplier = 'Firm XXX' (which would be included in the LeftJoin), and another row without the Supplier value, causing it to be omitted due to the use of the right anti join.

Any idea how to solve this?

[slorin]

Hi,

May be ?

Table.NestedJoin(
   TableB, {"B.SupplierArticleID", "B.Supplier"},
   Table.AddColumn(TableA, "Firm", each "Firm XXX"), {"A.No_", "Firm"},
   "NewColumn",
   JoinKind.FullOuter
)

Stéphane

[BA_Pete]

Hi @vonschultz666 ,

I may be misunderstanding your requirements, but you can perform a Full Outer merge in Power Query.

Once you get to this point:

let
    // Add prefix to column names in B and A
    RenameBColumns = Table.TransformColumnNames(B, each "B." & _),
    RenameAColumns = Table.TransformColumnNames(A, each "A." & _),

    // Filter B table for Supplier = 'Firm XXX'
    FilteredB = Table.SelectRows(RenameBColumns, each [B.Supplier] = "Firm XXX"),

... you can just Full Outer on A.No_ = FilteredB.SupplierArticleID:

Am I missing something?

Pete

[vonschultz666]

I think i forgot to mention one importatnt thing, full outer join in M can't have two conditions, therefore full outer join in SQL is a perfect fit for me, but in M not so much!

I would get only a subset of data with your proposal...

Any other ideas perhaps?

[BA_Pete]

It can't have two conditions in the way that you need it to i.e. the B.Supplier = 'Firm XXX' condition, but filtering table B on [Supplier] = "Firm XXX" before the merge satisfies this condition ahead of the merge, so you only need to satisfy the A.No_ = FilteredB.SupplierArticleID condition, which PQ can do just fine.

I'm obviously missing something here, so can you provide copyable example of both TableA and TableB before the merge that you would expect to become your example output table please?

Pete

[vonschultz666]

Hi,

ignore the above merged table (i've put something wrong), this is the right one:

Table B:

B.SupplierArticleID	B.Supplier
10000	Firm XXX
10000	Firm YYY
10001	null

Table A:

A.No_	A.Item
10000	Item 01
10002	Item 02

Result:

B.SupplierArticleID	B.Supplier	A.No_	A.Item
10000	Firm XXX	10000	Item 01
10000	Firm YYY	null	null
10001	null	null	null
null	null	10002	Item 02

[vonschultz666]

Any idea @BA_Pete ?

[BA_Pete · Accepted solution]

The change in the expected output table has changed the problem as I understood it.

If you're able to add a column into TableA specifying a single supplier, then the most efficient method would be the following:

Add a column into TableA that is just = "Firm XXX". Let's call it [A.Supplier].

Perform a Full Outer merge on [B.SupplierArticleID] & [B.Supplier] = [A.No_] & [A.Supplier].

You can do a two-field merge by using Ctrl+click on the fields in the merge dialog. Make sure to Ctrl+click the fields in the same order on each table:

This gives the following output:

Pete

[vonschultz666]

This works, thanks! 😄