How can I join two tables and keep both matched and unmatched values from the joining column?-CodePudding

I have two tables in Power Query that look like this:

Table A

ID	Col 2
1	A
2	B
3	C

Table B

ID	Col 2
1
2
3
4	D
5	E

Expected Outcome of joining tables (joining on ID column):

ID	Col 2
1	A
2	B
3	C
4	D
5	E

But when I perform a full outer join in Power Query (in order to keep unmatched rows of data), I get this:

ID	Col 2
1	A
2	B
3	C
	D
	E

I get the values from Table B Col 2, but I am missing the unmatched IDs from Table B.

How can I perform this kind of join in Power Query?

CodePudding user response：

Use Full Outer Join in Power Query

To accomplish this task follow the steps:

Select some data in the first table,
From Data Tab --> Click on From Table/Range Under Get & Transform Data.
This opens the PQ Editor, and select the Close & Load To From Home Tab, and import as Only Create Connection.
Follow the steps 1 To 3 for the second table and import it as a connection as well.
Now, from Data Tab --> Get Data --> Click on Combine Queries & Select Merge.
On selecting the above, a new window opens which shows as below, select the id for both the tables and select the join kind as Full Outer

Once now the PQ Editor opens, click on Advance Editor from the Home Tab, remove anything that shows and paste the following M-Code

let
    Source = Table.NestedJoin(Table14, {"ID"}, Table13, {"ID"}, "Table13", JoinKind.FullOuter),
    #"Expanded Table13" = Table.ExpandTableColumn(Source, "Table13", {"ID", "Col 2"}, {"ID.1", "Col 2.1"}),
    #"Merged Columns" = Table.CombineColumns(#"Expanded Table13",{"Col 2.1", "Col 2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Reordered Columns" = Table.ReorderColumns(#"Merged Columns",{"ID", "ID.1", "Merged"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"ID"})
in
    #"Removed Columns"

Note kindly change the table names as with your existing ones as well, and import the data again to excel to get the desired output you are looking for.

Updated M-Code:

let
    SourceOne = Excel.CurrentWorkbook(){[Name="TableOne"]}[Content],
    SourceTwo = Excel.CurrentWorkbook(){[Name="TableTwo"]}[Content],
    MergeTables = Table.NestedJoin(SourceOne, {"ID"}, SourceTwo, {"ID"}, "SourceTwo", JoinKind.FullOuter),
    Expanded = Table.ExpandTableColumn(MergeTables, "SourceTwo", {"ID", "Col 2"}, {"ID.1", "Col 2.1"}),
    MergedColumns = Table.CombineColumns(Expanded,{"Col 2.1", "Col 2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    ReorderedColumns = Table.ReorderColumns(MergedColumns,{"ID", "ID.1", "Merged"}),
    #"Removed Columns" = Table.RemoveColumns(ReorderedColumns,{"ID"})
in
    #"Removed Columns"

With the present M-Code, you need to do only three things, convert both the ranges into a table and open a blank query and paste the above M-Code by removing the existing one.

CodePudding user response：

Not sure if your data is oversimplified, but why not just append the two tables and remove the rows with null in Col2?

let Source = Table.SelectRows(Table.Combine({TableA, TableB}), each [Col 2] <> null) in Source