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

Joins illustration

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

This is the 2^nd of 2 posts on joins and is a complement to the Merge post.  Link to 1^st post.

---

In this exercise, we move beyond the conceptual and work through an exercise using 2 data tables to demonstrate all 6 types of joins.   

Data table 1, Account, contains a dollar value of $50.00.

Data table 2, EEID, can be matched to Data table 1 based on Company.  However, there are multiple rows where the Company is Dallas.  

After loading each of the data tables in to Power Query, Merge Queries as New was applied, matching on Company.  Account was the top table (left when viewed on a Venn diagram);  EEID was the bottom table (right on a Venn diagram).

Download the workbook below to view the data tables and each of the joins.  

                                                      ↑

---

Left Outer join

The left outer join takes the Account table (left) and brings back each row from the EEID table (right) that has the same company.  Notice how the amount of $50.00 is in the new table twice.  It's duplicated, because there are 2 rows in the EEID table where the Company is Dallas.

A VLOOKUP in native Excel returns the first match; a Merge in Power Query returns all matches.  This critical difference can dramatically skew your analysis if not handled appropriately.  Future blog posts, particularly on Power Pivot, will provide alternate solutions for how to handle multiple data sets with duplicate values in the matching column.    

---

Right Outer join

The right outer join takes the EEID table (right) and brings back each row from the Account table (left) that has the same company.  Notice how the $50.00 amount is again duplicated, since it matches 2 rows from the EEID table.  In addition, the Houston company has been added to the table, since the join starts with all company items in the right table.  

---

Left Anti join

The left anti join starts with the Account table (left) and returns a value if there is no match in the EEID table.  In this instance, the Company Dallas was in the EEID table, and so the resulting table is blank.

---

Right Anti join

The right anti join starts with the EEID table (right) and returns a value if there is no match in the Account table.  In this instance, the Company Houston was in the EEID table but not the Account table.

Anti joins are excellent audit tools to monitor exceptions or open items.

---

Inner join

The inner join returns values only when Company matches in both tables.  Notice the $50.00 amount is duplicated. 

---

Full Outer join

The full outer join returns every Company in each table.  Notice the $50.00 amount is duplicated. 

---

Append

Append is another option for combining tables using Power Query.  Append is designed to take an existing table and expand it by adding on another table that uses the same column names.  If a column name doesn't exist in the original table, new columns are added.  Notice that append picked up the 1 row from the Account table and the 3 rows from the EEID table exactly as they appeared originally.  There is no Company.1 column and the $50.00 amount is NOT duplicated.

---

💥BEST PRACTICE TIP
As a general rule, Append is intended to make a table longer, while Merge makes a table wider. 💥

---

Merge and Append allow accountants and analyst to perform a wide array of new analytics.  However, extra diligence is required to ensure the steps applied are yielding the analysis intended.

In this lesson, you learned how to:

• join 2 data tables using Merge;
• watch out for duplicates which could skew results; and,
• Append multiple data tables together instead of Merge.   

Prior post: Joins conceptually
Next post: 2 files 1 pivot table: Power Pivot

Joins conceptually

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

This is the 1st of 2 posts on joins and is a complement to the Merge post.

---

Let's assume you have two lists tracking journal entries on the first day of close: List A is entries booked and List B is entries expected. 

List A: Entries Booked Day 1	List B: Entries Expected Day 1
Cash	Cash
Rent	Utilities
Travel	Revenue

To merge these lists in Power Query, there are 6 types of joins available.

---

An inner join returns items if they are on both lists.  In this case, an inner join would return Cash.  Notice in the Venn diagram below that Cash is where both circles intersect.

An outer join in contrast will return all the items on the lists.  In this case, an outer join would return Cash, Rent, Travel, Utilities, and Revenue.  

---

A left outer join returns all the items on the first list.  Results would be Rent, Travel, and Cash.

A left anti join returns all the items on the first list that do not have a match on the second list.  Results would be Rent and Travel.

---

A right outer join returns all the items on the second list.  Results would be Utilities, Revenue, and Cash.

A right anti join returns all the items on the second list that do not have a match on the first list.  Results would be Utilities and Revenue.

---

The left outer join is closest to a VLOOKUP in native Excel.  Similar to a VLOOKUP, Merge is looking up items from a list, finding a match and returning the result. 

Contrary to a VLOOKUP, Merge returns each instance where there is a match, not just the first match it finds.

---

When using Merge in Power Query, the table on the top is the left side (List A) and the table on the bottom is the right side (List B).

---

The next blog post demonstrates the results of each type of join in Power Query and illustrates how duplication of records can occur. 

In this lesson, you learned about:

• the 6 different join types in Power Query; and,
• how to do a "VLOOKUP" across multiple columns using Merge.

Prior post: Remove duplicates
Next post: Joins illustration

Choose your own Data Adventure

New to this blog? Start here.

Choose Your Data

Time to load and transform your own data using Power Query (PQ).

Choose a workbook, report, or query which you regularly modify. Good candidates for this intro level exercise require recurring scrubbing or processing such as:

• Splitting, combining, or removing rows or columns
• Extracting data from columns
• Combining multiple files with identical layouts
• Changing header names
• Adding a column that uses simple conditional logic (VLOOKUPs in the same worksheet or IF statements)
• Adding a column that contains a simply mathematical operation
• Performing a VLOOKUP from one file to another

The goal is to select a file that allows you to gain confidence using your new Powers by flexing your muscles; the goal is NOT to take steroids and blow your muscles up unnaturally, disfiguring the results.

---

Get Your Data

In Excel, start a New workbook.

Office 365, Excel 2016, and Excel 2019 users
In the ribbon, go to the Data tab, Get & Transform Data section.

Excel 2010 and Excel 2013 users
In the Power Query tab, go to the Get External Data section.

All users
Explore the importing options available.

Some of the most popular options for finance are:

Choose the data source type corresponding to the source for your data.

Browse to your file and select “Import”.

On the pop-up Navigator screen, your choices will vary depending on the source file type. The example below is the pop-up screen after choosing an Excel workbook as the source.

•     Selecting the folder icon "Query CC transactions" imports all the subitems.
•     Selecting Table1 or Working_Budget_Query brings in those Excel Tables.
•     Selecting All_Trans brings in that worksheet in the Excel workbook.
•     Selecting any item will provide a preview of the data to be imported.

Once a selection has been made, select "Transform Data" for newer Excel versions, or "Edit" for Excel 2010-2013.

---

Explore the Power Query Editor 

Below the ribbon, the left side contains the Queries menu. If your import included multiple items, you can locate the other queries by expanding the Queries menu.

The center contains a preview of the columns in your query, but a maximum of 999 rows. Any changes made in PQ are stored in your new workbook and do not affect your original source file.

The right side has Query Settings.

Under Name, rename the query using the following best practice standards.

No spaces. If it's a data query (like GL activity), add Data in the name. If it's a lookup query (like a chart of accounts), add LU in the name. In our prior exercises, the queries were named "Bank_acct_LU" and "GL_bal_Data".

Notice in the above Applied Steps area, PQ has already recorded some steps. These will vary depending on your data source. Selecting the gear next to Source or Navigation will take you back to the Navigation prompt. Selecting the X to the left of any step will remove that step.

---

💥BEST PRACTICE TIP
There is no undo in Power Query. Be careful when removing steps. If you accidentally delete a step, you can close the query and discard any changes; however, you will loose all changes made during the current session.

---

Explore the PQ Menus

Let's discuss the 3 tabs where most processing takes place: Home, Transform, and Add Column.

• Home steps can be applied to the entire query.
• Transform steps apply to selected columns.
• Add Column steps result in adding new columns to your query.

Interestingly, the same step may be listed in different tabs, but execute differently. For example, Extract on the Transform tab extracts the requested information from the existing column, leaving it behind, and removing what is left. Whereas, Extract on the Add Column tab keeps the existing column as is and creates a new column with the extracted data.

---

The Home tab

Try adding some of the Home tab processes as steps to your query. If the step doesn't perform as expected, select the X next the Applied steps to remove it.

Close & Load is the final step of the query process. This loads the query in to the Excel workbook. Outputs are either an Excel Table, a Pivot Table, a Pivot Chart, or Connection only.

Manage Columns and Reduce Rows allows for quick cleanup of your file, removing rows or columns. In addition, you can remove only blank rows, only duplicates, or rows that have errors. An error gets flagged in PQ when the data in a column does not match the data type for that column.

To sort, select a column and the appropriate ascending or descending button. To sort by multiple columns, select the second column to be sorted and repeat. It will be applied as one step with multiple sort criteria.

Split Column is a powerful PQ function. Explore the numerous options available including split By Number of Characters far right or far left, which replaces the LEFT and RIGHT functions in native Excel. Splitting By Delimiter, Custom, can include a full string of text, compared to native Excel's single character split.

Group By aggregates data for a designated column. Similar to building a Pivot Table, but inside a query. Columns not specified in the Group By dialog box are removed from the query.

Data Type specifies the type of data that is in a selected column. Any data in a column that does not meet the data type characteristics will result in an error. For example, data type Decimal Number will result in an error if the value in a row is "text".

Use First Row as Headers and Use Headers as First Row allows the promotion or demotion of the first row or headers.

Replace Values leaves an audit trail to allow others to follow the transformation from the source file to the revised analysis. Something that native Excel's Find & Replace doesn't provide.

Merge Queries, Append Queries, and Combine Files allow the blending of multiple data sets replacing VLOOKUPs or their counterpart INDEX, MATCH. There is a lot left to explore on this subject; when getting started, keep it fairly simple, like our examples in the Intro Series exercise.

---

The Transform tab

The Transform tab performs actions on selected columns. The menu options available differ based on the data type for the selected column(s). You'll notice some duplication of commands from the Home menu. This allows you to perform all column transformations without flipping between two menus.

Some of the key processes to consider trying on the Transform menu are described below.
Transpose allows you to flip rows and columns, similar to Paste Special, Transpose in native Excel.

Reverse Rows flips all rows so that the bottom row becomes the top row and vice versa.

Replace Values has an expanded option to Replace Errors for cleaner reporting.

Fill Up and Fill Down fill in blank spaces above or below each cell.

Initial data in native Excel

Data after loading to PQ

 Transformed using Fill down

Move includes quick shortcuts to move a column to the beginning or end of your query.

Format is used to tidy up text records using Trim or Clean.  It also provides options to change the case.

Merge Columns combines selected columns, separating them by a designated character or string.

Extract is a powerful data cleansing tool with multiple options for plucking out the data you need from a cell, replacing LEFT, RIGHT, MID, and FIND functions in native Excel.

Standard is a quick way to perform simple mathematical operations on a numerical column. Try "Multiply, "-1" to flip the sign of all numbers in the applicable column.

Rounding includes Round Up, Round Down, and Round.

Date has enough functions available for its own upcoming blog post.  Most options are self explanatory and expand your ability to transform a date column quickly and easily.

The Transform menu should give you plenty of options to start working your own magic in PQ.

---

💥BEST PRACTICE TIP
Learning new tricks is an investment.  Consider building out a few steps in PQ, loading the query and finishing your transformation in native Excel.  This progressive approach blends both worlds until PQ becomes second nature (which it quickly will with a little practice).💥

---

The Add Column tab

The Add Column processes add a column to the query. Some of the functions are the same as under the Transform tab, except Transform performs the action on the selected column and Add Column keeps the selected column and adds an additional column with the action performed: the duplicate ones to explore are Format, Merge Columns, Extract, Standard, Rounding and Date.

Column From Examples was developed for people learning PQ.  Input samples of values for your new column and PQ will try and create the column.

Custom Column can be used to create various types of columns.  Try creating a text column by entering ="Text".  Or, create a mathematical column by inserting two columns that are multiplied together.

Conditional Column creates a column that if a value meets specified criteria then the output should be as defined.  If not, additional rules can be applied or a null value will be the output.

Duplicate Column duplicates.

---

Hopefully this has been a successful data adventure for you. It's time to wrap up with a Close & Load and start analyzing the results.

In this lesson, you learned how to:

• Use your own data to start automating using Power Query;
• Choose different data sources to load to Power Query;
• Modify the query name;
• Remove Applied Steps; and,
• Create automated processes from the Home, Transform, and Add Column menus.

Prior post: The Reward, Month 2
Next post: Remove duplicates


💬  Your comments drive my posts.
What can I teach you next in order to move you forward in the revolution?