As I was working on one of the assignments for our upcoming Power Query training course, it occurred to me that I’ve never blogged about this feature: how to retrieve related tables in Power Query.

Retrieve Related Tables in Access

If you’re using Access, you’re stuffed.  I mean… you can do it manually then merge the tables.  Here’s the database I connected to:

Yet when I pull the tblChits table, I get the following columns:

All the columns from the tblChits table, but nothing from the other tables.  Too bad really.

So basically, you can still retrieve related tables in Access, you just need to create a connection to each table, then merge the queries manually.  The steps to do this, in brief:

• Create a “Connection Only” query called Chits that points to the tblChits table
• Create a “Connection Only” query called Items that points to the tblItems table
• Create a “Connection Only” query called Categories that points to the tblCategories table
• Create a new query that references the Chits query
• Merge the Items query, making the relationship between the POSItemCode in both queries
• Merge the Categories query, making the relationship between the POSCategoryCode in both queries

So basically, we can still retrieve related tables in Access, we just need to understand the relationships in our database and merge them manually.

Retrieve Related Tables in SQL

In SQL server, it actually pulls some things over for you nicely.

In this case I connected to the AdventureWorks database that we’re hosting in Windows Azure for course participants to use.  (Does anyone else provide an Azure hosted database to practice with?)  Specifically, I:

• Connected to the Azure hosted AdventureWorks database
• Selected the SalesOrderHeader table (only)
• Filtered down the dataset to get a shorter table
• Removed a bunch of unnecessary columns from the SalesOrderHeader table

And look what I’m left with:

The Sales.SalesOrderDetail contains a full table of related records for each row in my table.  Those related records come from a completely separate table that I didn’t even ask for, how awesome is that?

And the Sales.SalesTerritory column shows a “value” for each, which also has more data that can be expanded (including sub tables):

Pretty slick, and saves me the effort of having to sleuth out and perform the joins manually.  I sure wish Access had this ability as well!

Don’t have an Azure database?

This technique works for SQL server (on prem) as well.  I’m not 100% sure which other databases support this technique, as I don’t have access to others, but if you do know, please comment here and I’ll add them to the list.

And if you really wish you had access to try out sourcing data from an Azure database… it’s not too late to sign up for our course!