Creating lookup tables in PowerPivot using SQL queries

On the last post I showed how to model for a scenario in which two datasets must be merged. A custom lookup table was created using the 'remove duplicates' functionality in Excel. In this post I will walk through the same scenario, but this time using the SQL query wizard in PowerPivot to achieve the same result. This post is intended for power users who will benefit from creating robust PowerPivot data models.