Merge: Power Query's VLOOKUP

New to this blog? Start the Intro to Power Query series here.

Merge is like a Power Query VLOOKUP. 

In Power Query, Merge links two queries together by joining matching columns. In the Intro to Power Query series, Merge will be the final step in building the automated Treasury report.    

---

MERGE

Start by opening the SOLUTION Process Bank or continue building your own file created from the prior "Process Bank Account Listing" post.

If you are outside of the Power Query Editor, then Merge is an option directly from the Query menu. When your cursor is in one of the tables output by the queries (such as cell A1), you'll find a Query tab at the top of the ribbon.  Select the "Merge" button.

If you are in the Power Query Editor, then select "Merge Queries" from the Home menu.

In the new Merge dialogue box,

• Select the queries: GL_bal_Data & Bank_acct_LU from the drop down menus;  
• Hold Ctrl and select the two "GL Account" columns. This makes the first match.  
• Continue pressing Ctrl and select the two "Dept" columns.  This makes the second match.  Notice the 1 indicating that the GL Account columns should be compared to each other and the 2 indicating that the Dept columns should be matched.  
• Select "Ok".

---

A third query has been created called "Merge1".  Let's rename it "Report".

Now let's take a look at what's been created.  The first 4 columns are exactly the same as the GL_bal_Data query.  A 5th column was added, which represents the Bank_acct_LU query. 

We can expand the Bank_acct_LU column by selecting the double arrows on the top of that column.  Deselect "Use original column name as prefix".  Then select "Ok".

PQ has now created a merged table that shows each column from both queries, lined up by GL Account and Dept.

---

Finalize the Query

For illustrative purposes, we brought in all the columns from both queries.  However, GL Account.1 and Dept.1 can be removed since they are redundant.  Hold Ctrl and select the two columns; then, right-click on one of the columns and select "Remove Columns".

Let's rearrange the columns, so they are in the following order:

To move a column, select the column and drag it while holding down the left mouse button.

On the Home tab, select the "Close & Load" dropdown, then "Close & Load To".  In the Import Data menu that pops up, select a Table in a New worksheet.

---

Finalize the report

We are all done with PQ.  Time to work in native Excel for the final cleanup.

With your cursor in the new Report Table, go to the Table Tools, Design menu, and select the Table Styles, None, in the top left corner.

• Make the headers bold.  
• Make the Balance column, currency with two decimals.  
• Insert three rows above the Table.
• Add a title in row 1: "Available Cash at Monthend".
• Add a title in row 2: "As of October 31, 2018".
• Two rows below the Table add a footer: "Total Available Cash at Monthend".
• Add a total balance, formatted bold with a double underline.
• Hide gridlines.

---

Congratulations!  You've completed the build out of the new report.

🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈

Next month, this is now a 3-step process.

• Choose the files (GL data and bank listing)
• Process (right click in the Report Table and select Refresh)
• Analyze

---

In this lesson, you learned how to:

• Merge two files together to create one query,
• Expand a column containing a Table,
• Rearrange columns, and
• Take a Table as a query output to create automated reports within native Excel. 

SOLUTION

Prior post: Automation

Next post: The Reward, Month 2