Many business users rely on Excel’s powerful VLOOKUP function to pull information from different tables and sheets (even different workbooks) before summarizing and analyzing 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, and then summarizing the data with the COUNTIFs, SUMIFs, INDEX and MATCH for reporting purposes.
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.
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. The Power BI data modeling feature allows users to connect a bunch of tables from different sources together using a simple drag-and-drop function. 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.