How to create relationships between two tables that have more than one unique value?

[mdrammeh]

Hello Everyone,

I am very new with Power BI/ Power Query and seeking your help to resolve this scenario:

Scenario:

I have a set of tables from two companies. Each company has a different process but their data is structured in the same format. For each company, I have been given a total spend and target by zone, location, employee, date etc.. in two separate tables.

The goal here is to combine the two tables into a single table to create a relationship. The table with the spend has multiple transactions repeated overtime for each company. The targets are grouped by zone, location, employee, date etc.. in two separate tables but only once for each row. In short, I have one table with MANY transactions and the other table with ONLY ONE transaction for each row.

Therefore, I need to create a relationship between the two tables so that I can calculate the variance between the sum of all spend by zone, location, employee, date etc..

I have tried to append the two queries together but for whatever reason, some of the values are being duplicated. Also, when I tried to force a relationship between the two tables but I keep getting an error message.

Could someone walk me through how to resolve this issue in Power BI and Power Query?

Thanks for all your help in advance.

Dra

[MattAllington]

How are you trying to combine the tables. There is no reason the data should be duplicated. It is just a matter of appending one table to the other. Alternatively you can create a set of lookup tables ( read here https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/ ). 

Personally wihth this data, I would append the tables together. 

[mdrammeh]

Hi MattAllington,

Thanks for the reply and link in your response. If the tables are the same, I would append as suggested but what I have is a scenario where one table has the budget and the other table includes the spend. Because of this reason, I thought there might be a way to create a relationship using DAX,

I am still new in this game and hoping to learn from experts like yourself. Please feel free to respond below if you have any other suggestions for me. I will review the link you sent in the meantime.

Thanks again!

Dra

[Eric_Zhang]

---

@mdrammeh wrote:

Hi MattAllington,

Thanks for the reply and link in your response. If the tables are the same, I would append as suggested but what I have is a scenario where one table has the budget and the other table includes the spend. Because of this reason, I thought there might be a way to create a relationship using DAX,

I am still new in this game and hoping to learn from experts like yourself. Please feel free to respond below if you have any other suggestions for me. I will review the link you sent in the meantime.

Thanks again!

Dra

---

@mdrammeh

Then unpivot those two tables at first and append those two tables.