Power Query: (Data) Model Building
1 February 2017
Welcome to our new Power Query blog. Today I extract a table from an Access Database and load it into the Excel data model ready for use by Power Pivot.
So far in this blog, I have concentrated on extracting data from files and folders. For this example, I will extract data from a table in a Microsoft Access Database. Although I could use this data in an Excel workbook as in the previous examples, this time I will add the table to the Excel data model, so that it can be used by Power Pivot.
A quick glance at the drop down from the ‘From Database’ tab reveals a large selection of different kinds of databases that can be accessed in order to extract data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/pq-08-image1.png/cfb58df87186d40b6827a0533000fb58.jpg)
Choosing the ‘From Access Database’ option allows me to browse for the database that I will use. Having selected my database, the navigator screen lists all the tables in that database. I choose to preview ‘ACCT_Order_Charges’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/pq-08-image2.png/708f41061711d078987b5edf40f8461a.jpg)
I could load the table as it is, but instead I elect to ‘Edit the ACCT_Order_Charges’ query. The Query Editor should be familiar to regular readers of this blog, and it allows me to remove redundant data – in this case the ‘Discount’ column – which is clearly not of use.
In order to do this, I can either select the ‘Discount’ column and right-click to find the ‘Remove’ option, or I can use the ‘Remove Column’ option on the ‘Home’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/pq-08-image3.png/b68007c2ed7ce2991b727c552cd42293.jpg)
I can then use the ‘Close and Load’ option from the ‘File’ tab or the ‘Home’ tab, but in either case I make sure I tick the ‘Add to data model’ box. I also choose the ‘Connection Only’ option, which is also useful for merging tables / queries – in this case I don’t need to see the table in the workbook.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/pq-08-image4.png/271188ef8baa335b01339ef9f3cd5c70.jpg)
Since I have Power Pivot, I can go to the Manage (data model) option; ‘ACCT_Order_Charges’ is visible in the ‘Workbook Queries’ pane.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/pq-08-image5.png/38bdac96f7c2b278ca0a724433a0509c.jpg)
Choosing to ‘Manage’ allows me to view the ‘ACCT_Order_Charges’ table in detail, ready to refine the data further by adding calculated columns and creating and managing relationships with other tables in the model.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/pq-08-image6.png/b179d68903fb36d95d4f1c58afd5c732.jpg)
So if we can edit the table in Power Query and Power Pivot, are there any problems to look out for? I look at this in more detail in the next blog…
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!