MS Excel — Use #PowerQuery to Compare Two Lists
#TwinzTechTip #TwinzTalk
A frequent task in business is to compare two lists of data.
Have a master customer, product, mailing list etc and need to compare it to another list to see what is missing and needs to be added? Typical scenarios…
Basic Power Query makes this easy!
Please don’t do this by hand. 😳
Common approaches include using Excel MATCH or VLOOKUP function (which generates errors which indicate items aren’t on one list).
However, power query makes those methods tedious by comparison!
Here are the steps to using Power Query:
1️⃣ Create a query from the MASTER list. “Connection Only” query.
2️⃣ Create a query from the comparison list. This is a “Connection Only” query as well.
3️⃣ Now, “compare” the 2 queries. This is done by MERGING the 2 queries and using an “Anti-join” relationship — so that just exceptions are listed.
4️⃣ Load it as a Table into Excel and you have your list! Want to see how this is done for real?
Work through this approach once — and you won’t go back!
Interested in seeing the file — you can download it here.
