Power Query Challenge – Joining two tables fully (Part 2)

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:

Power Query Full Outer JOIN Revisit

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   

Excel Tip - Power Query Full Outer Join Revisit 1

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.

Excel Tip - Power Query Full Outer Join Revisit 2

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

Excel Tip - Power Query Full Outer Join Revisit 3

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

Excel Tip - Power Query Full Outer Join Revisit 4

 

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!

Excel Tip - Power Query Full Outer Join Revisit 5

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

Excel Tip - Power Query Full Outer Join Revisit 6

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.

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Power Query and tagged , . Bookmark the permalink.

1 Response to Power Query Challenge – Joining two tables fully (Part 2)

  1. p45cal says:

    Another way: Merge Sales and Traffic with Full Outer join, add two conditional columns, remove most of the other columns:
    let
    Source = Sales,
    MergedQueries = Table.NestedJoin(Source, {“Store”, “Date”}, Traffic, {“Store”, “Date”}, “Traffic”, JoinKind.FullOuter),
    ExpandedTraffic = Table.ExpandTableColumn(MergedQueries, “Traffic”, {“Date”, “Store”, “Traffic In”}, {“Date.1”, “Store.1”, “Traffic In”}),
    AddedConditionalColumn = Table.AddColumn(ExpandedTraffic, “Custom”, each if [Date] = null then [Date.1] else if [Date.1] = null then [Date] else [Date]),
    AddedConditionalColumn1 = Table.AddColumn(AddedConditionalColumn, “Store1”, each if [Store] = null then [Store.1] else if [Store.1] = null then [Store] else [Store]),
    RemovedOtherColumns = Table.SelectColumns(AddedConditionalColumn1,{“Custom”, “Store1”, “Trans Count”, “Unit Sold”, “Net Sales”, “Traffic In”}),
    RenamedColumns = Table.RenameColumns(RemovedOtherColumns,{{“Custom”, “Date”}, {“Store1”, “Store”}}),
    SortedRows = Table.Sort(RenamedColumns,{{“Store”, Order.Ascending}, {“Date”, Order.Ascending}}),
    ReplacedValue = Table.ReplaceValue(SortedRows,null,0,Replacer.ReplaceValue,{“Trans Count”, “Unit Sold”, “Net Sales”})
    in
    ReplacedValue

    Like

Leave a Reply to p45cal Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.