When you just start working with PowerPivot or PowerBI this feature (performing vlookup) just jumps right off the bat and makes it a very compelling case for Excel users to shift to the BI tools.
In most cases this change is permanent!
But these tools are not just made for common lookups they can do a lot lot more things that you can’t even imagine doing in the excel. At-least not with ease
In this post, I’ll explain
- How can you perform a VLookup in PowerPivot / PowerBI
- Practical use cases – when and when not to do it
Consider these 2 Tables
For answering any of these questions, you’ll have to apply a bunch of Vlookups between Sales and Products table
- Total Sales by Year and Months
- Total Sales by Product Type
- Total Sales by Category
Instead of going the conventional way let’s solve this using PowerPivot
Vlookup in PowerPivot / PowerBI
Since I wanted to explain a few nitty gritties I recorded a quick video. You’ll learn how to create relationships between tables and how to write the RELATED Function. Both of these are equivalent of writing a Vlookup in Excel