Forum Discussion
Merging and Appending Data Tables
Hello,
I am trying to merge and append data from two tables where the first one has an employee name, ID and their travel to and from information. The second table has the employee location - city and country. This location has no bearing on the To and From country data.
Travel Data:
| Name | ID | Department | To | From |
| Bob | 1 | Sales | CA | US |
| Bob | 1 | Sales | US | |
| Andy | 2 | Marketing | CA | UK |
Employee Location:
| Name | City | Country | |
| Bob | 1 | London | United Kingdom |
| Bob | 1 | Brisbane | Australia |
| Bob | 1 | New Delhi | India |
| Bob | 1 | Berlin | Germany |
| Andy | 2 | Boston | United States |
| Andy | 2 | Paris | France |
The resulting table should merge the Travel and the Employee location data and Travel Data and if needed add rows for each employee as needed:
| Name | ID | Department | To | From | City | Country |
| Bob | 1 | Sales | CA | US | London | United Kingdom |
| Bob | 1 | Sales | US | Brisbane | Australia | |
| Bob | 1 | Sales | New Delhi | India | ||
| Bob | 1 | Sales | Berlin | Germany | ||
| Andy | 2 | Marketing | CA | UK | Boston | United States |
| Andy | 2 | Marketing | Paris | France |
I am able to merge the data, but I can't add the new rows where the country to and from fields remain blank for the newly added rows. Would anyone know how I could do this?
Thank you for your help!
newpbiuser01 , Add nested index in both tables as suggested by Curbal - https://www.youtube.com/watch?v=7CqXdSEN2k4
Then Merge, in the merge. Select two column with CTRL select first ID and then Index in both tables and do left join with first table
merge: https://radacad.com/append-vs-merge-in-power-bi-and-power-query
2 Replies
newpbiuser01 , Add nested index in both tables as suggested by Curbal - https://www.youtube.com/watch?v=7CqXdSEN2k4
Then Merge, in the merge. Select two column with CTRL select first ID and then Index in both tables and do left join with first table
merge: https://radacad.com/append-vs-merge-in-power-bi-and-power-queryThanks amitchandak that worked like a charm!
