Real-world data is always messy. There will be always in need for adding new columns from one table to another because no table will have complete information. Adding a column allows for more efficient data analysis as well as the ability to add additional information to the table. Adding a column from one table to another helps us to combine data from separate tables that have related information this is an added advantage to draw meaningful insights from data. By getting columns from one table to another we can understand relationships between the tables easily which makes it easier to analyze the data for making decisions based on the information.

Steps to Get a Column from One Table to Another in Power

For a better understanding, we need to see this by using a practical example. Consider two tables,

sales_table: Which contains sales information of products in INDIA for the year 2019,2020. To access the sales_table click           

sales_table

product_table: This contains information about the products and their owners who bought them in INDIA for the year 2019,20202. To access the product_table click 

product_table

Here we wanted to get the product_owner column from the product_table to the sales_table for this we have 4 different ways.

Four Ways to get a Column from One Table to Another

1. The first way is by using the Related function.

What RELATED Function do in power-bi? The name itself indicates that this function returns the value from the other related table. This function works when we have a relation between the tables.

Syntax : Related(columnname)

DAX Code :  using_related = RELATED(product_tabel[product_owner])

 

2. The second way of doing this is by using the LOOKUPVALUE function.

LOOKUPVALUE function is used to retrieve the value from another table. The name itself indicated that it looks up the value from one table to another related table. Both the LOOKUPVALUE function and RELATED functions perform the same operation but the only difference is SYNTAX and the internal operation of the RELATED function. The LOOKUPVALUE function syntax is difficult to understand when compared to the RELATED function. Whereas the RELATED function itself relates to the table without any manual specification. This function works when we have a relation between the tables.

Syntax: LOOKUPVALUE(Result_columnName,Search_ColumnName1,Search_Value1,…..,[Alternate_Result])

• Result_ColumnName: It is the name of the column from which you wanted to get the column.
• Search_columnName: The column that contains search_value. 
• Search_Value: The value that you want to find in the search_column.

DAX Code: using_lookupvalue = LOOKUPVALUE(product_table[product_owner],product_table[product_name],sales_table[product_name]) 

 

3. The third way of doing it is using the CALCULATE Function.

CALCULATE function is the most used dynamin function in power bi. It works as a base function to apply another function through DAX. Here by using CALCULATE function we are applying the VALUES function to get distinct values according to the relationship between the tables. The VALUES function is used to get distinct values when a column name is given to know more about the VALUES function you can go through this blog values function.

SYNTAX: CALCULATE(Expression,[filter1],[filter2]….)

• Expression: It is an expression or function which we need to apply.
• Filter: Filters conditions are applied according to the expression given.

Note: This function works when we have a relation between the tables.

DAX Code: CALCULATE(VALUES(prouct_table[product_owner]))

 

4. The final way to do it is by using the Merge queries option in Transform data.

• Go to Transform data.

 

• Click on merge queries

 

• A dialog box opens with sales_table with product_table below it, select the columns which have one-to-many relation with each other. Select full outer join. (here I am using full outer join we have many types of join conditions so select according to your need) and then click on OK.

 

• A column is created in our sales_table with all the columns from the product table. See the screenshot added for a better understanding.

 

• Select the column which you want to get from the product table. Here I need product_owner names from product_table to sales_table

 

• Click on close and apply then you can observe the changes reflected in the sales_table by adding a new column.

 

Here is the OUTPUT screenshot using merge queries.