Combining Data and Queries

In order to explain the process of combining data, we use an scenario for better understanding.

Let us take the data of various states, considered useful for building additional analysis efforts and queries. But on of the issue that arises is that much of the data provided uses a two-letter abbreviation for state codes and not the full name of the state. Now, you are required to associate state names with their abbreviations. Given, another public data source to combining data, a good amount of data shaping is required before we can connect it to our retirement table.

Steps to shape the data, include –

• Firstly, from the Home ribbon in Query Editor, select New Source > Web.
• In the second step, enter the address of the website for state abbreviations, and then select Connect.
• Then, the Navigator displays the content of the website.
• Select Codes and abbreviations.

Combining Queries

After shaping the data you get the StateCodes table required. Then, you may combine these two tables, or queries, into one. Since the tables we now have are a result of the queries we applied to the data, they’re generally referred to as queries. Primarily, there are two ways to combine queries including merging and appending.

• You may merge the queries when you have one or more columns that you’d like to add to another query.
• Else you may append the query when you have additional rows of data that you’d like to add to an existing query.

Steps merge the queries

• Firstly, from the left pane of Query Editor, select the query into which you want the other query to merge. In this case, it’s RetirementStats.
• In the second step, select Combine > Merge Queries from the Home tab on the ribbon.
• Then, the Merge window appears. It prompts you to select which table you’d like merged into the selected table, and the matching columns to use for the merge.
• Next, Select State from the RetirementStats table, then select the StateCodes query. When you select the correct matching columns, the OK button is enabled.
• For expanding the merged table, and select which columns to include, select the expand icon
• In this case, we want only the State Code column. Select that column, clear Use original column name as prefix, and then select OK.
• Lastly, to apply your changes and close Query Editor, select Close & Apply from the Home ribbon tab.