Connecting to SQL tables automatically brings in related tables

[DataSkills]

Hello there, 

I am connecting to a SQL data source for a real estate project. What I find is that I connect to my Units, it seems to automatically pick up some kind of link to the Buildings table.

When I look at the columns in Power Query, I see that at the end of the Units table, there is a "Values" column with the expansion arrows for the fields from the Building table. I suspect that in SQL, there is some kind of predefined relationship between these 2 tables. 

If I click the expand button (marked with an arrow above), it shows me the fields from the Buildings table. 

Ordinarily, I would bring in Units as a table and Buildings as a table and then create a relationship between the 2. 

What is best practice in this situation? My guess is that expanding that column will introduce a lot of duplicated data and using a traditional setup of 2 tables with a relationship on BuildingID would be a more efficient method of setting this up. 

Thanks

[AlexisOlson · Accepted solution]

I find this feature useful and it is indeed based on SQL table relationships. If the related table is a dimension table that doesn't increase the number of rows in your current query (no rows in your original table connect to more than one row in the related table), then it's a nice convenience, especially if you're doing something like denormalizing a snowflake schema into a star schema.

If expanding the related does create additional rows (i.e. if there are multiple rows in the related table corresponding to a single row in your original table), then you need to be much more careful about duplicated data. Probably best to avoid unless you know exactly what you're doing and why.

[HotChilli]

I don't think there is a best practice, it just depends on the model you have and the performance and design implications of having the 'include relationship columns' option checked on the SQL connection.

If you have a SQL model which is relational and a powerbi model which is dimensional, you wouldn't want to have the option checked.  Different model, different requirements. You would want to maintain control over what returns from SQL.