Power Query gives you the capability of joining (merging) tables together using key columns, and there are multiple types of Merge types to choose from. There is another way to merge tables especially when the data source is a relational database system. In this article and video, You will learn how this options works.
Video
Include Relationship Columns
In Power BI or Power Query, when you get data from a relational database system, often you see an option called “Include Relationship Columns”. The screenshot below shows this option when you get data from a SQL Server data source;
This option is only available for certain relational database sources (Such as SQL Server, Oracle, DB2, Snowflake etc). It won’t be available for Excel for example, because there is no concept of relationship in Excel.
Power Query can understand the metadata structure of the relational database system, and one of those metadata information is the existing relationship between two tables. Power Query can then bring the second table as a column at the end of the first table and that is called “Relationship Column”. When you enable “Include Relationship Columns”, you are telling Power Query that you would like these columns to be added in the tables in Power Query Editor.
Relationship Columns are what you see in the screenshot below;
Easy way to Merge
The relationship columns are Structured column data type. Meaning that they are either Table, Record, List, or Value data type. Compared to other columns in a table they don’t have a simple value, they have an object (table, record, list, or value) including multiple values in it.
In this case. Territories are the territories that this employee is related too.
The relationship columns won’t be loaded into Power BI when you click on “Close & Apply”. The reason for that is complex data structures (Table, Record, List and Value) aren’t accepted in Power BI Sematic Model as a data type.
The main benefit of relationship columns are the ability to expand into the underlying table and flatten the structure of two tables together. For example, Expanding the Territories from Employees will happen like the below screenshot;
Once you expand, the combined view of both tables will apear as the result;
This is an easy way to merge the two tables without even knowing what is the key column (primary key and foreign key) that connects the two tables together.
You can use this approach to flatten the tables from the data source to create a Dimension table for the purpose of Star Schema. The article below explains a process of merging tables to create a flatten dimension table;
Summary
“Relationship Columns” are only identified by Power Query when the data comes from a relational database system. These columns can be used to merge tables easily without even knowing the joining columns. If you want to learn more abour related subjects, read my articles below;
- Merge vs Append in Power Query
- Different types of Merge
- Dimension table: Flatten structure from other tables
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.