Power Query: Double Access Savings – Part 3

Welcome to our Power Query blog.  This week, I continue to extract 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 extracted the database files to Power Query, and filtered them.  I then chose to ‘Combine Files’ using the icon to the right of the Content heading.

I clicked on the folder icon, and clicked ‘OK’ to combine the files in Power Query.

This time, I will continue examining and combining my data in the Power Query editor. 

A number of steps have been generated by the ‘Combine Files’ process, and I can now see the tables in both of the Access databases.  This will be my base query, as it will allow me to access the data from any of my tables.

I take a reference copy of Access Databases by right-clicking on it in the Queries section.  This allows me to add more steps which are specific to the table I am extracting data for, without changing Access Databases.

I rename the reference query Commodity Groups and click on the word ‘Table’ in the Data column on the row where Name is ‘Commodity_Groups’:

This creates a Navigation step and displays the data from the Access database table.

I take another reference copy of Access Databases and repeat the steps to create the Commodity Sub-groups query.  

Next time, I will combine the data and look at how to load only the final query to Excel.  I will also look at what happens if an Access database file is open when the query is refreshed.

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