Power Query: Two (Queries) Become One

Welcome to our new Power Query blog.  I have previously shown how to extract a database table and load it into the Excel data model.  Today I create a query for a second table and show how queries can be merged.

Power Query allows me to merge tables without either the need to be an expert on database structure or without having to learn formulae.  I only require two (or more) existing queries.

I already have a query to load data from ACCT_Order_Charges, created in Power Query – (Data) Model Building, and I want to merge data from another table so that I can include a description of the type of items that the charges apply to.

As I did for the first query creation in that blog, I create a new query from an Access database; on the ‘Power Query’ tab in the ‘Get External Data’ section, I select ‘From Database’, and then choose the Access database where the tables reside:

In the ‘Navigator’ window (shown above), I can preview the table contents and choose the table that contains the data I am looking for, in this case the Items table. Note that the previous query is visible in the ‘Workbook Queries’ pane to the right of the screen. If the ‘Workbook Queries’ pane is not displayed, there is an option on the ‘Power Query’ tab in the ‘Workbook Queries’ section to ‘Show Pane’ accordingly.

I select the ‘Edit’ option and choose to extract the Item_Group and Item_Name columns from Items. As the reason for this extraction is to merge the data with the query on ACCT_Order_Charges, I also choose to extract the Item_Key column to allow me to link the data together. I select these three columns whilst holding down the SHIFT key and by right clicking, I then choose to delete the other columns.

I do not wish to make further changes, so from the Home tab I select ‘Close and Load to Connection Only’.

Note that the Items query appears in the ‘Workbook Queries’ pane, but is not complete yet. On the ‘Load To’ screen, the option to add the data to the Excel model can also be selected if required. Having made my selections as shown above, I choose to ‘Load’.

Now to join the queries – on the ‘Power Query’ tab there is a ‘Combine’ section where I can choose to ‘Merge’, and the following window appears where I choose my two queries:

The dropdown at the bottom of the screen allows me to choose how to link the tables; in this case I choose ‘Left Outer’, as I want all of the ACCT_Order_Charges table and matching data from Items (if I had input Items first then I would pick ‘Right Outer’). This is the point where some understanding of the data is required – picking ‘Full Outer’ can lead to duplicates.

The ‘OK’ button is not enabled yet – that is because I need to pick the columns to join. In this case I want to join Item_Key in both tables, so I select it on each table. As long as I select the same number of columns in each table, then ‘OK’ is enabled, which I then choose.

The ‘Query Editor’ below shows all the columns from ACCT_Order_Charges, and a new column helpfully called NewColumn. Note the icon next to it with two arrows pointing away from each other – this will expand to show the new columns available:

Now all the columns can be viewed, there is the option to uncheck columns that are already in the first table to prevent duplication. In this case description is very similar to Item_Name so I uncheck Item_Name and Item_Key (since I only included the key to enable me to link the table data).

I click ‘OK’ and the data from Items appears with a slightly clunky column name (see below) which I can change to Item_Group.

Note that the order of the rows has changed because the non-linked rows which had no item key have moved to the bottom. I can fix this by sorting on Order_Key and Order_Line_Number – there is a downward arrow icon next to each column heading which I can click and then choose to sort in ascending order – notice that Power Query combines these sorts into one step. I also choose to rename the table to something more meaningful than Merge1.

I can now ‘Close & Load’ to create a new merged table, which can be used in Excel workbooks and the Excel data model. When merging queries do keep in mind the warnings from last time’s blog; if the queries are for use by Power Pivot only, then consider whether it would be more useful to load one merged table or separate tables that can be managed in Power Pivot. For creating Excel workbooks however, merging is a useful way of pulling in data from many tables in order to view the data in a single table. Data can of course come from other external sources.

Next week I will look at extracting data from a website…

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!