Tables.GetRelationships() Function In Power BI M

There’s a new M function in the latest release of Power BI Desktop that I spotted: Tables.GetRelationships(). Here’s the help page for it:

image

Basically, what it does is return all the relationships between all of the tables in a data source such as SQL Server. The documentation is a bit cryptic but I worked out what it does.

When you use a function like Sql.Database(), you get a table that contains a list of all of the tables in a database. For example, if I use the expression:

Sql.Database("localhost", "adventure works dw")

On my local instance of the Adventure Works DW database, this is the table that is returned:

image

This, it turns out, is the “navigation table” that the Tables.GetRelationships() function needs for its first parameter. The column called “Data” in this table, which contains links to the actual tables in the database, is what Tables.GetRelationships() needs for its second parameter. Put the two functions together in a query like this:

let
    Source = Sql.Database("localhost", "adventure works dw"),
    Rels = Tables.GetRelationships(Source, "Data")
in
    Rels

And you get the following table:

image

Expand all the columns here and you get a table with one row for every relationship detected between every table in the database:

image

Useful if you need to report on the structure of a database, I guess. It’s a shame that this isn’t available in Power Query in Excel yet (it isn’t as of September 2015, I assume it’s coming soon) because it would be cool to use this data with NodeXL.

14 thoughts on “Tables.GetRelationships() Function In Power BI M

    • When this function comes to Power Query in Excel (hopefully in the next few weeks) it should be possible. However I’ve spent some time trying to feed the output of a Power Query query into NodeXL (I wanted to blog about this) but it’s not as easy as you might think: copy/paste is probably the best thing you can do for now.

  1. It’s unfortunate that MSFT doesn’t strive to maintain release feature symmetry between Power Query and Power BI. I do get the sense that Power BI is getting more attention – which makes me sad.

    • To be fair, I think they do quite well. It usually takes less than a month for new functionality to show up in Power BI and Power Query. I’m not sure whether they will be able to maintain this with Excel 2016 though.

  2. I have the same concern – now that Power Query isn’t an add-on. Do they have a mechanism to continue doing monthly updates?

  3. Ah – didn’t realize that click-to-run did continuous update checks. I like the subscription model more due to that aspect. But I am concerned that it appears to be the case that you can’t share dashboards except with other subscribers. Am I wrong about that?

  4. Thanks for guiding us through this function.

    Remark:
    “Expand all the columns here and you get a table with one row for every relationship detected between every table in the database:”
    In case of compound keys there will be multiple rows. My suggestion would be to add Index columns just before expanding the “Columns” column and again just before expanding the “OtherColumns” column, so information is preserved which Columns belong to which key.. Additionally you may want to add columns with list counts of those 2 columns (before expanding of course).

    Question:
    This function doesn’t seem to work for tables in Power Pivot, even when keys are added to the tables using Power Query Formula Language (M) and relationships are created in Power Pivot, an error is returned: “Expression.Error: We can’t get the relationships. A primary or unique key is required to perform the operation against the table.”.
    Would this be a bug or can it logically be explained?

  5. At first all actions were in Excel.
    I took the following steps:
    1. Created some tables in Excel.
    2, Created “From Table” queries, adding a key to each table using Table.Distinct.
    Query output only to Datamodel.
    3. In Power Pivot (Manage Data Model) I created relationships.
    4. Created a query to test Tables.GetRelationships using the table names from step 2.
    (In this query I also used Table.Keys to verify that the keys were still there).

    After your feedback I tried the same in Power BI by importing the Excel file in Power BI (which loaded the tables and their relationships) and repeated step 4 in Power BI, with the same error message mentioned in my original post.

    Maybe, in these cases, Tables.GetRelationships is just looking at the individual tables, disregarding the relationships in the datamodel in Excel (Power Pivot) and in Power BI?

    • I think Tables.GetRelationships() is only intended for use when the source tables are coming from a relational database – I wouldn’t expect it to find relationships in the Data Model.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s