I think that one of the most crucial skills that any marketing, and any entrepreneur for that matter, can have is a good mastery of excel. Data is so important in managing your business and measuring your success, so learning just a few trick with the tools that you already have at your fingertips can help you to be more efficient and, in-turn, more effective in decision-making.
I originally created the tutorial below to show my CEO how we could merge the monthly sales information that we is getting from the 4 payment platforms that we use, Apple, Play, Stripe, and PayPal.
Why merge data into one table?
First one all, having data in one table makes it easier to compare information across platforms. Once the data is in one table a simple pivot table can capture all of your information without the need of making multiple worksheets or table, that might become cumbersome.
Secondly, having can help you to avoid error when you see everything in one place you can compare your figures easily and make sure you are capturing all information correctly.
Lastly, time management. Just use PowerQuery to set-up your merge one time. When you add new data, just update date your merge and voila, everything neat and easy to use the next month.
What is a merged table?
It is taking all of the information from two or more table and combining them together into one master table.
When merging you can choose to keep:
- All information from the two tables
- Only the information that matched the rows of the first table
- Only the information that matches the second table
- Only the information present in both tables
To merge two tables one thing must be true. They must have a column (or multiple columns) in common). So this might be your customer code, the date of the transactions, whatever field you can share among the tables.
Step 1: Create Tables
Unless you would like to repeat information it is important that you have a unique column in each table to be able to match them.
Unique means that there are no duplicates.
It is, however, not important that both tables contain 100% of the same rows. (I will explain this more later). In this simple example all of the rows match.
To create your tables:
- Input your data into excel.
- Select the data you want in the table.
- Press CTRL + T
- Click DESIGN from the top menu
- Name your table in the left hand box “Table Name”
Note: It is not necessary to name the table but it helps you to remember what data is included in this table.
Step 2: Import Tables into PowerQuery
Select the first table but clicking on any cell in the table.
- Go to Data
- From Table / Range
- A window from PowerQuery will open (If you don’t have PowerQuery in your Excel, go to the File -> Options and Comm Add-Ins to add it to your workbook)
- You have a second opportunity to rename the table on the right panel in QUERY SETTINGS
- Select CLOSE AND LOAD TO on the top left
- Check ONLY CREATE CONNECTION
- Click LOAD
- Do the same for all of the other tables
Step 3: Merging the table (from Excel)
The merge can be performed both from inside PowerQuery and directly from excel. If you start the process in Excel, the PowerQuery window will automatically open.
- Select the two tables that you would like to merge
- 6 rows of each table will be shown of each table and all of the columns
- In the pulldown below the table select the JOIN KIND that you would like to make
- LEFT OUTER mean the you are adding data from the second table to the first
- RIGHT OUTER means you are adding data from the first to the second
- FULL OUTER means all rows of the two table must match (and you keep all)
- INNER means you only keep the rows that match
- LEFT ANTI means that you only keep the rows from the first one and add data from the second (rows that don’t match from the second will be excluded)
- RIGHT ANTI means same as left anti but from the second table
Step 4: Select the matching columns
In each table select the columns (as mentioned above) that should match between the two tables and press okay.
Below you will see how many rows are matched between the two tables.
In our simple example, 4 out of the 4 rows are matched.
Step 5: Select columns to keep
A window of PowerQuery will open with your merge. In the last column to the right you will see the second table and no data yet added.
- Click on the double arrows to the top right of table 2
- Select the columns you would like to merge
- Deselect the column that is already present in the other table. In this case “subject”
- Select ‘Okay’
Step 6: Rename columns & Merge
The new columns will appear with a name in this format tablename.columnname.
- Double click the header of the column to change the names to something more agreeable.
The name does not have to be the original column name (this is particularly helpful if you have a column in the first table with the same name and would like to keep both columns.)
- Change the name of the merge in the right hand QUERY SETTINGS box
Step 7: Close and Load To Table
Time to create the final merged table.
- In the left left select CLOSE AND LOAD TO
- When the dialog box appears select TABLE if it is not automatically selected
- Choose where you would like to place the table (I recommend a new worksheet)
- Select load
- Et voila! Now you have a new table.
Step 8: Final Result & Making Changes
Your final result will be a green table in the worksheet that you have chosen.
Any changes that you make to the two original tables will be updated in the merged table.
It is also possible to merge another table. To do this. merge the third table to the already merge table and create a new merge. In this case, when you read the final step, select ONLY CREATE CONNECTION from the dialog box until you have merged all of the tables together.
And that’s it! With those short steps you have your merged table. Just learning small tips with excel will help you to organize you data much better and make it more actionable.