- Apr 1
Power Query Merge
- Neale Blackwood
- Data, Power Query
- 0 comments
Merging in Power Query is a useful feature that automates and replaces XLOOKUP functions. But please make sure the look up table doesn’t have duplicates.
Sign up to hear about free Excel training.
I won't share your email with anyone.
1 min read
In the image below the table on the left has accounts and amounts. The table on the right is to be used to convert the account numbers in column A into new accounts.
The problem is the table on the right duplicates the code 1234 (rows 2 and 6 both have 1234). This causes an issue when you merge the two tables together – see image below.
Note there are now four 1234 rows instead of two. Because the account 1234 was in the lookup table twice, it doubled (duplicated) the 1234 rows in the merged table.
Always make sure the lookup table has unique entries to look up. You can use the Remove Duplicates option in either Excel or Power Query on the lookup table before you merge.