Add Columns

[Anonymous]

Hi Community,

I am new to power bi and i am facing below issue, Please help me figure out.

I have 2 data sets One data set have 3 coulmn and 2nd data set have 5 columns. I want to compare both the data sets and if the record matches then copy the values from 2nd data set to 1st data set.

Dataset-1

Emp id, emp name, emp address

1            vamsi          Hyd
2            Kiram           Bengalore

Dataset-2

Emp id, emp name, emp address, emp ph, emp birthyear

1            vamsi          Hyd                9848881482  1990
2            Kiram           Bengalore     9871222124   1994
3            vamsi          bengalore       1234567889   1987

I need output for data set-1 should be like below, 

Output

EmpID, empName, emp address, emp ph, empbirthyear
1            vamsi          Hyd                9848881482  1990
2            Kiram           Bengalore     9871222124   1994 

Thanks,

Paruchuri

[parry2k]

set the relationship on emp id and drop the column from respective tables in a table visual.

[Anonymous]

I have duplicate values in both tables so i can not create relation ship. 

[v-chuncz-msft · Accepted solution]

@Anonymous,

Remove duplicates first, then you may use LOOKUPVALUE Function in DAX.

[drewlewis15]

1. In the query editor, select Dataset 1, and navigate to "Merge Queries" on the Home tab (far right).
2. Dataset-1 will already be selected on the top, so choose Dataset-2, and join on Emp id
3. Make sure that Join Kind is Left Outer
4. Click OK
5. You now have a new column in Dataset-1 called "Dataset-2" with a table in each row.
6. Click on the double error next to Dataset-2 to expand the tables, and choose the following fields
   1. emp ph
   2. emp birthyear
   3. And de-select the option to "use original column name as prefix"
7. You should now have the desired table.