Vlookup in power query

See more:

MS-Excel

BI

Hi, I am using Power Query (excel 2013) tool to work on vlookup process for large data (3+lakh records). I used "Merge" method and got the result. two questions i am having.

1. Left Outer merge option is similar to VLOOKUP but not the same! In standard Excel, when a match is found, only the first matching value is returned. In Power Query, all the matching rows would be recreated. But i need only the first matching value. How to attain it.

2. As i have 3lakh records (Say FileA), i want to find the serial# for 3 lakh records by merge option. i have around 6 files which need to be matched with FileA. Once match is found i dont want to include those records for the rest of 5 files. I dont know how to filter and work on the records. Expecting your help.

Thanks,
Karan

What I have tried:

2. loaded FileA(say 1000 records) and apply merge option. i got result for few records (Say 600). But i want to search the next next files for the rest 400 records (once match found those records can be omitted for next next searchs