Append Queries + Group By = Another alternative for a better Full Join

In the previous post, we explored an alternative way to perform FULL OUTER JOIN with Left Outer Join + Right Anti Join, followed by Append Queries in order to get a result with better layout.  Well, as it’s Excel, that means there are different ways to achieve the same.  In this post, let’s see how we can do the same using Append Queries + Group By.

Again a picture tells thousand words.  The following diagram illustrates the concept:

In the end of this post, I will post a video (work in progress 😅) riding on the example in previous post to demonstrate it.  From which you will see the two different approaches yield the exactly the same result.

For illustration purpose, two simple tables are used for this post.  Let’s see how to do it step by step in Power Query!

You may download a Sample File to follow along.

A. Load the two tables into Power Query

Select a table, then

1. Go to Data tab
2. From Table/Range   

Close and Load to Connection only.  Then repeat the above steps for the other table.

Now we should have the two tables in Power Query Editor.

B. Append Queries as New

In the Power Query Editor

1. Go to Home tab
2. Click the drop-down menu of Append Queries 
3. Append Queries as New
4. Select the primary table
5. Select the table to append
6. OK

C. Group By 

1. Select the columns (Attribute 1 and 2) we want to group
2. Go to Transform tab
3. Click Group By
4. There is where we need to input the new columns and the operations we need, one by one:
   1. New Column Name – manual input.  In our case, we want to keep the original headers.  That’s why we input Value 1, 2, 3 respectively.
   2. Operation – Select from drop-down menu.  There are common operations like SUM, AVERAGE, MIN, MAX, etc there.  In our case, we select SUM.
   3. Column – Select from drop-down menu.  There are all the columns in the current table.  Select the column where you want to apply the Operation to.  In our case, we select Value 1, 2, 3 respectively.
   4. When we are done with one column (Value1), click the Add aggregation and repeat the above for Value2 and Value3.
5. OK

In summary, we ask Power Query to create a new column called “Value1” by summing the values in the original column “Value1“;  group them by Attribute1 and Attribute2.

In plain language: Give me a summary table by grouping Attribute1 and Attribute2; sum the values as summary.  Think about what we do with Pivot Table. 😉

Here we go!

Let’s Close and Load the result to worksheet and see!

The two tables are JOINED, FULLY! 🙌

Isn’t it cool?

Some considerations:

1. If there are lots of values in the two tables, it’s a bit tedious to do the Add aggregation in the Group By step.
2. I guess there may be performance issue on BIG table with lots of values.  I haven’t tested it nor compared it with the approach using two merges + append.  If you know the answer, please leave your comments below.
3. It works only if you want to pull the values (numbers that we can apply SUM operation to) of the two tables together. It doesn’t work if you want to put attributes (text cannot be summed).
4. Any other thoughts? Please share with us by leaving comments below. 🙂

Video is now ready coming soon.  Please enjoy stay tuned. 😉

You may down a Sample File  with the previous example here.