How does Power BI solve the Multiple Datasets problem?

When we need to complete a task in Excel, it is sadly rare that all the information that we need is all held in one dataset. We often need to get a base dataset which has most of the information that we need and then use lookup functions to grab data from lookup datasets. If you’ve ever spent hours wrangling Excel formulas like VLOOKUPs, INDEXMATCHes or XLOOKUPs you know how fragile and time-consuming dataset joining can be. Not only is using these lookups time consuming to join base datasets and lookup datasets, they’re inefficient to update and prone to error. This is the multiple datasets problem and Power BI solves it!

Power BI solves solves the multiple datasets problem with intuitive relationships, drag-and-drop joins, and a visual model that makes sense—even to beginners. Unlike Excel, where repetitive reports require manual updates and formula gymnastics, Power BI automates the process. You build once, refresh forever. Power BI is not just faster—it’s smarter. It understands your data structure and lets you join datasets with just a few clicks.

There are two Power BI joining options; a merge, which physically joins tables together, or relationships, which join tables virtually. Merging particularly solves a number of problems, giving you the option to fuzzy match datasets, when the values in your key column don’t quite match (the columns that you’re attempting to use in your lookup to make sure the correct rows are matched). It allows you also to join datasets when there’s more than one matching row, rather than just ignoring any rows after the first match. It’s also so much faster to bring in multiple columns from a lookup dataset into a base dataset. Instead of writing one lookup function for each column you want, in Power BI you join the datasets once, and then pick and choose the columns you want. Much easier and faster!

Video Chapters

00:51 – What is the multiple datasets problem?
01:42 – Why is Power BI better at using data from multiple datasets?
04:10 – What’s the process to join datasets in Power BI?

Short On Time?

The Multiple Datasets Problem

When someone needs to combine data from different tables together, the vast majority of people use Excel to do the work. This normally works by…

Grabbing the dataset that has most of the information that you need – the base dataset
Obtaining the supplementary datasets that has additional datasets – the lookup datasets
Using Lookup functions like XLOOKUPs to transfer columns from the lookup dataset to the base dataset, ensuring that the correct information is matched to the correct row

This process is time consuming, particularly when a report needs to be generated repetitively, prone to error, inefficient to maintain and not great when the reports inevitably grow. Power BI can solve the multiple datasets problem using a ‘merge’ function in Power Query, or using ‘relationships’ in the Data Model. Both these options have significant advantages over Excel.

How does Power BI solve the Multiple Datasets problem?

By using the merge function, which literally joins two tables together or using relationships, which create a virtual join, Power BI can solve the multiple datasets problem. Here are some scenarios where Power BI can help:

If you need to transfer lots fields from lookup tables to base tables, in Excel you need to use a separate lookup function for each field. In Power BI, you can create a merge in 5 mouse clicks and then just click on the fields you want. Faster and more accurate
If the key columns have slightly different values, what do you do? Excel lookup functions don’t have much flexibility in this scenario. However, Power BI offers fuzzy matching to deal with data inconsistencies
What happens if your key column in your lookup dataset has more than one matching row? Excel lookup functions will retrieve the first row that matches and ignore the others. Power BI’s merge function will find and retrieve all matching rows. See here to see this one in action!
What happens if you want to find rows that don’t match between datasets? In Excel. you’ll need to perform lookups and then remove any rows that aren’t errors. Power BI has different ways of joining data. Some of those ways keep matching data, and some of those ways keep non-matching data
What happens if your lookup datasets change structure or increase their size? In Excel you’ll probably need to reperform all your lookup functions. In Power BI you’ll often just save over your old lookup datasets with the new ones, then click ‘Refresh’. Power BI will sort out the rest!

…and a little more

Relationships are ways of joining tables together virtually rather than physical joins like merging does. This means that the tables are only joined when you use data from each dataset in the same chart. This is easier for the Power BI engine to process than very wide tables. However, there is a place for both merging and relationships in Power BI! I still use merging very frequently as in some cases they are far better than relationships. They’re also more intuitive for the beginner to understand!