Power Query: Double Access Savings – Part 4

Welcome to our Power Query blog.  This week, I combine the data from the tables in the two Access databases.

I have two Access databases.  These are deliberately very simple, as I am demonstrating a concept here.  The first database (imaginatively called ‘Commodity Groups’) contains the Commodity_Groups table:

In another database, in the same directory, I have the Commodity_Sub_Groups table.  Funnily enough, the name of this database is ‘Commodity Sub-groups’:

Last time, I took reference copies of Access Databases and extracted the tables from each database to create the Commodity Groups and the Commodity Sub-groups queries  

I want to combine the data in my queries by ‘Merging as New’ from the Home tab:

I want to preserve the original queries, so this will create a new query.   In the dialog, I select the tables, and join them on Commodity_Group_Key using a Left Outer join.

This gives me a reference of Commodity Sub-groups and new column containing all the data for each row of the Commodity Groups query. 

I use the icon to the right of the Commodity Groups column heading and choose what data I need from Commodity Groups. 

I only need the Commodity_Group_Name, and since that is not a column already, I don’t need a prefix.   I also rename the query Commodity Sub-groups and Groups:

This query is the only one I want to load to Excel, so when I ‘Close & Load’ I select the ‘Close & Load To…’ option, otherwise all the queries will be uploaded:

Rather than having to reset and delete the default of sheet for every query, it’s best to ‘Close & Load To…’ here and then sort out what will be loaded in Excel, when the sheets and cells can be specified.  In Excel I choose to ‘Only Create Connection’, as I will change this just for one query.

I can then right-click on the Commodity Sub-groups and Groups query in the ‘Queries & Connections’ pane, and choose ‘Load to…’

This triggers a familiar dialog, but it will only apply to this query:

I change this to Table and opt to put it on the ‘Existing worksheet’:

Finally, a warning.  If I hadn’t included the filter on the file extension on the Access Databases query in Double Access Savings – Part 2, then if I had one of the Access databases open and tried to refresh Commodity Sub-groups and Groups, this would happen:

This is because opening the database creates a temporary file with extension ‘.laccdb’ which is not recognised as a database.  This is why I need that filter even if the folder I am extracting from only contains Access databases.  With the filter in place, opening the database file has no impact on the query.

Come back next time for more ways to use Power Query!