Power Query: (Data) Model Building

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:

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’:

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.

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.

Since I have Power Pivot, I can go to the Manage (data model) option; ‘ACCT_Order_Charges’ is visible in the ‘Workbook Queries’ pane.

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.

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!