Merging queries

Power Query is indeed powerful Excel add-in that allows retreiving data from different locations, changing them and preparing for further processing. Data sets are retrieved by creating queries to the data source, and the result of each query is stored in a separate table. Sometimes it is necessary to merge the results of two queries. The following text will explain how this works.

The operations of joining, adding and linking queries occur within the Query Editor, about which you have had the opportunity to read in one of the previous “recipes”.

There are several reasons why we would merge queries. In my opinion, one of the most useful ones is when we directly access the database, import two tables, and then use the INNER JOIN technique to eliminate all those rows where there is no correlation between two imported tables. For example, let’s connect to a database that contains two tables: Partners and Contacts. Import these two tables by creating two different queries. Matching the data, or extracting all those partners for which there are appropriate contacts, is done by selecting the Merge Queries option from the menu with the same name that lies within Combine group of Home ribbon. In this menu, there is also the Merge Queries as New option. These two options differ in that the first result is returned in the current one, and the second creates a new query.

After launching the Merge Queries as New option, a window will open where you need to choose which tables (queries) are being merged and what type of connection exists between them. Choose the Partners and Contacts tables, and the Inner Join connection type. Then select the key through which the connection is made which, in this case, in both tables, is the ID-P column.

After confirmation, a new Merge1 query will be created containing the results of the previous operation, which is a list of all partners with associated contacts. In order to be able to see the contacts, you need to expand the query, which is done by clicking the icon to the right of the title of the Contacts header, and then by choosing option Expand.