Many business users rely on Excel’s powerful VLOOKUP function to pull information from different tables and sheets. This is an important step before they can even analyze data.
This is a traditional approach many analysts still use to this day. However, with the increasing amount of data and a need for quick insights; Power BI’s ability to build robust relationships between various sources of data should be considered.
The VLOOKUP approach
The process would usually start with adding extra columns to the main data set, using VLOOKUPs. Then an analysit will summarize the data with the COUNTIFs, SUMIFs, INDEX and MATCH.
Essentially, the VLOOKUP formula creates a relationship between different tables. It’s a powerful Excel formula that effectively solves a lot of problems and has been a favorite tool of analysts for many years.
The problems with VLOOKUP start when data sets become larger. When using VLOOKUPs, INDEX and MATCH, SUMs and IFs in tables with few hundred thousand rows, you’re putting Excel to its limit.
Other problems with VLOOKUP include manually fixing ranges, columns, copying formulas down after adding new data. Of course, you should always remember to reference cells with the F4.
Relationships in Power BI
Microsoft Power BI takes relationships seriously. Power BI allows users to connect many tables from different sources together using a simple drag-and-drop. No more formulas to maintain, it’s done a lot faster and even beginners can learn it quickly. All you need are related fields in tables and relationships are ready to be made.
Apart from this, the benefits of using Power BI relationships are also the ability to automatically update data when adding / removing rows; inserting or deleting columns from the tables without having to remember the order of columns and most importantly working with large data sets.