Forum Discussion
Helper IHow to mimic SQL "Full Outer Join" in M?
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
Helper II 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?
Super User
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
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