Retrieve Related Tables in Power Query

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:

image

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

SNAGHTML24047fb1

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:

SNAGHTML240c8e12

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?

SNAGHTML240e1703

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

SNAGHTML240f07eb

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!

4 thoughts on “Retrieve Related Tables in Power Query

  1. I was just experimenting with this today in the Power BI Designer using the ODBC Table driver. I think it will bring in tables only if Foreign Key relationship is set up. For the two MySQL sample databases I used, one had FK and the other didn't.

  2. FYI, tried a quick Access DB, added Foreign Keys, but Power Query didn't see a relationship.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.