Power Query: Double Access Savings – Part 2
3 August 2022
Welcome to our Power Query blog. This week, I filter and expand the data in the two Access databases from last week.
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1657280972.png/82c6d2da0187b6cde144db473855f52b.jpg)
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’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1657280995.png/2d97d228dd0d1bc2ac8e492ff7b73970.jpg)
Last time, I used ‘From Folder’ to extract the databases into Power Query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1657281123.png/bd2639e3886407a526c5822e165e670d.jpg)
I then chose to transform my data in the Power Query editor:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1657281146.png/355042b05d9009d4b3631c414c01984b.jpg)
This week, I shall start by filtering my data, so I only keep the files I want. For this example, I could add other databases to the folder, so I want to allow these to be selected, but I want to exclude the large ‘Access Database SP’, as I do not need tables from this database and including it could slow down my query. In the filter dropdown on Name, I unselect ‘Access Database SP’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1657281171.png/f19f92a960b906d4e782a8e39e4f1547.jpg)
There is another filter I need to do, in order to ensure that I only pick Access database files. I need to use ‘Text Filters’ in the filter dropdown for Extension, as there is only one value currently, so I can’t select it on the checkbox without selecting everything:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1657281193.png/3eb6f7caec6bf5d93afb97209ef58b2e.jpg)
I choose only those files where the Extension equals ‘.accdb’. This is the only item in the dropdown for the value.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1657281213.png/a4c71c8205d83b0fa600728784517978.jpg)
Note that I must include this step even if there are only Access database files in my folder; I will show why later. Now I have the files I need; I can choose to ‘Combine Files’ using the icon to the right of the Content heading.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1657281235.png/3393fedfc2f51d9fa5944c74c1f007ad.jpg)
(Also notice that Power Query has not managed to combine the ‘Filtered Rows’ and ‘Filtered Rows1’ steps!)
This takes me to the ‘Combine Files’ dialog:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1657281259.png/ffb04b089742c54ef008d2d36baabafa.jpg)
I can click on the table icon to check the data, but I must not click OK, as the process would then try to append the data in the tables.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1657281282.png/94eaa52d5e5c878da417c2c774a61d69.jpg)
Instead, I click on the folder icon, and then I can click ‘OK’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1657281302.png/c49a6b27b93c19e3e2ebb85622f53285.jpg)
Next time, I will continue examining and combining my data in the Power Query editor.
Come back next time for more ways to use Power Query!