When to use Merge or Relationship in Power BI? I have come across this question many times and decided to write a blog post about it.

Merge: A Merge is when you have one or more columns in one Table and you want to add the columns to a related Table using a common field.

I have Customers table in SQL Server and Orders table in SharePoint (Two different locations). I want to create a report of Total Orders and Price by Customer in Power BI, so I will need to add Quantity, Unit Price and OrderDate from Orders Table to Customers Table to create the Merge Query.

The two tables have a common field called CustomerID which is Primay Key in Customers and Foreign Key in Orders, so using either Merge or Relationship will give me the Total Orders for each Customer.

For this scenario, Merge is more efficient because I have the option to Disable Load of the original queries into the data model. That means, less memory consumption and better performance. When the Power BI data model grows bigger, the original queries won’t be loaded into the memory.

In addition, when merging a cloud datasource to On-Premises datasource,
for the gateway to connect to the cloud datasources through a proxy, you need to update the proxyaddress attribute with your proxy information from one of the following files, in the configuration section:

• C:\Program Files\On-premises data gateway\ Microsoft.Mashup.Container.NetFX45.exe.config.
• C:\Program Files\On-premises data gateway\m\ Microsoft.Mashup.Container.NetFX45.exe.config.

<configuration><system.net><defaultProxy useDefaultCredentials=”true” enabled=”true”><proxy proxyaddress=”http://192.168.1.10:3128” bypassonlocal=”true” /></defaultProxy></system.net></configuration>

For more details, check following link: https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-proxy

To create a Merge Query:

• Select the first Query from the left
• From Home tab, select Merge Queries as New as seeing below
• Select the Left Table, for this example the Left Table is Customers
• Select the Right Table, for this example Right Table is Orders
• Select the matching column CustomerID for both Tables as seeing below
• Click OK

Power Query create a new Merge query for you.

At the end of the Merge query, you will see a new column with all values from (Orders).

• Click the expand icon to select the required columns from Orders: OrderDate, Quantity and UnitPrice as seeing below
• Click OK

• Right click on Customers Query and uncheck Enable load as seeing in screenshot below
• Repeat the same steps for Orders Query
• Click Close & Apply

When you load the data to Power BI, only a single table (MergeQueries) that combines the two tables is loaded to the data model. The original tables Customers and Orders won’t be loaded into the data model, hence less memory consumption and better performance.

Relationship: A relationship is where two or more tables are linked together by a common field because they contain related data. By default, Power BI Desktop autodetect the relationship during load.

From below diagram, Customers Table and Orders Table are related by CustomerID.

When you create or Power BI autodetect relationship between Tables, all Tables are loaded into the memory. When you compare the two PBIX files with the same imported data, you will see that the Relationship file is larger in size than the Merge file.

However, its depends on the scenario why you want to use Relationship or Merge in Power BI, like using some DAX functions for analysis that may not work when using Merge. Also, if you have a complex model, it is recommended to do as many modelling at the Datasource level. The less complex the model is, the better the performance in Power BI.

Happy Data Modelling 🙂