How to merge tables that have two keys

[tiberiomena]

Hi guys, how are you?

I've been trying to merge two tables, but both of them have two keys. In SQL it's easy to do that with select * from... but it seems that Power BI can't.

This are my tables: A is a product sales (with date of sale), and B is service sales (with date of service). I want to merge these two tables since product service retentions depends on what product were sold. I attached a image for reference:

 

As you see, I want to know what sales and service were done monthly, by customer.

Please, your help will be greatly appreciated.

Regards,

Alexis

[SivaMani · Accepted solution]

@tiberiomena,

As per your requirement,

1. Generate Date table
2. Dimension table for Vendor

Join the above table with TableA and TableB

[Anonymous]

Many-Many relationship is not supported in power BI. Hence you cannot merge Table A and table B. Instead you can try the alternate by having a master table for Vendor(i guess you are using the Vendor field to merge the two tables in SQL) 

1) Create a query in Power BI to get the distinct Vendor from Table A and table B and the name resulting table as VendorMaster

2)Create relationship within PowerBI VendorMAster->TableA and VendorMaster->TableB

Then you can create a measure to get the required result