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:
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:
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:
[sourcecode language=”text” padlinenumbers=”true”]
let
Source = Sql.Database("localhost", "adventure works dw"),
Rels = Tables.GetRelationships(Source, "Data")
in
Rels
[/sourcecode]
And you get the following table:
Expand all the columns here and you get a table with one row for every relationship detected between every table in the database:
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.
Hi Chris, very useful post; do you a know a technique to synchronize this PBID “relationship table” with the NodeXL template?
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.
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.
I have the same concern – now that Power Query isn’t an add-on. Do they have a mechanism to continue doing monthly updates?
Yes, but it’s Office 365 click-to-run so you need an Office 365 subscription
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?
Just found this: https://support.powerbi.com/forums/265200-power-bi/suggestions/6872640-share-with-external-users-outside-my-organisation. And it’s by far the highest rated item.
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?
Can you explain how you’re using the function with Power Pivot? Are you connecting to a Power Pivot model using Power BI?
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.