Merge Related Table Rows Text Column in Power BI

Scenario:

In the Power BI report, we aim to display all technology names associated with the current project using a card or any other visualization. The technologies are stored as a sub-grid within the Project form, with a many-to-many (N:N) relationship between Project and Technology, implemented through the bit_technology_project table.

Issue:

In Power BI, the CONCATENATEX function cannot be applied at the row level. Therefore, we cannot create a new column using DAX to merge technology names in Project table.

Solution:

1. Open Transform Window, by transform Data button on Home Tab of Power BI Desktop.

• Download Primary and Related Tables using Get data. In my case they are Project and bit_technology_project.

• Add reference table for related table i.e. bit_technology_project. Right click on related table and click on Reference as shown below:

• Remove unwanted columns and then new Reference table will look something like this:

• Group By table using Project ID, so that each row will reference to single project. To do that click on Group By button in Transform tab of ribbon.

• Select Project column (In my case it’s msdyn_project) in grouping option in Advanced tab. Give Column name to be created and select All Rows in Operation field as shown below:

• Click on Ok and new column will look like:

• If you click on Table of any row in TechnologyGroupColumn column, it will open table, and you can get column names of table. For e.g. I will copy bit_technology column name which holding value of technology names for future use.

• Navigate back to reference table and create new custom column. For that click on Custom Column of Add Column tab of ribbon buttons.

• Create Custom Column using below formula where TechnologyGroupColun will be your grouped column created on step 6 and bit_technology will be column name inside grouped column table as we got from step 8

• Click Ok and it will create list column:

• Click on Symbol next to TechnologyNames column and select Extract Values

• Select any delimiter you want between technology names. I am selecting comma.

• Result will look like:

• Click on Close and Apply on Transform window. Now you have Project Table and Technology reference table. Create 1:1 relationship between these 2 tables by navigating to Model -> Manage relationships.

• Create 1:1 relationship between bot tables like this:

• You can now select newly created TechnologyNames column in Card or any other visualization you want.