Comparing Two Sales Tables

[Gurpreetsingh1]

Hi Guys, Hope you are doing well.
I am working on a report in this report I have two  sales tables one is from SAP and Another is from Salesforce. And I have find match mismatch between both of them. 
1st Approach :- I am merging them in power query editor based on sales order columns from both,But the problem is in that I have duplicate orders becoz of multiple products for same orders in both tables and in that case I am geeting extra line while merging. eg (first table rows 4, 2nd 4 rows so. 4*3=12) rows. But here should get 4 rows max.

2nd  Approach :- I have made a composite key by merging sales order & product code colum in both tables now i am merging them again based on combined key here all result is good but  i am missing the data where my product code is not matching However I want to show both matched unmatched in same table.

Desire result :-

I want both matched unmatched orders in same table and want all orders to show along with the amount, product code from both tables so I can compare further differences.

Sales OrderProduct Code (SAP)Amount (SAP)Product Code (SF)Amount (SF)Match Type

SO101	PRD001	5,000	PRD001	5,000	✅ Full Match
SO101	PRD002	3,200	PRD008	3,000	❌ Product Code Mismatch
SO102	PRD003	2,500	PRD009	2,400	❌ Product Code Mismatch
SO103	PRD004	4,000	PRD004	4,100	⚠️ Amount Mismatch
SO104	PRD005	1,800	—	—	❌ Missing in SF
SO104	PRD006	2,200	PRD006	2,200	✅ Full Match
SO105	—	—	PRD007	2,800	❌ Missing in SAP

Thanks in Advance 

Data as an example SAP 

Sales Order	Product Code	Amount (SAP)
SO101	PRD001	5,000
SO101	PRD002	3,200
SO102	PRD003	2,500
SO103	PRD004	4,000
SO104	PRD005	1,800
SO104	PRD006	2,200

Sales Force 

Sales Order	Product Code	Amount (SF)
SO101	PRD001	5,000
SO101	PRD008	3,000
SO102	PRD009	2,400
SO103	PRD004	4,100
SO105	PRD007	2,800
SO104	PRD006	2,200

[m4ni]

hi @Gurpreetsingh1 

There are always different ways to achieve this.  I prefer the DAX soution as its easier for me at least.

If you have the composite key, keep that.

Create a table in DAX as the following:

Compare =

VAR Table1 = SUMMARIZECOLUMNS(Table1[Key])

VAR Table2 = SUMMARIZECOLUMNS(Table2[Key])

RETURN INTERSECT(Table1, Table2)

This will give you eveything which matches between the two tables.  

For the unmatched rows, you need to decide whch table is the primary and secondary for the comparison.

If table 1 is the primary or master then use this code:

Compare =

VAR Table1 = SUMMARIZECOLUMNS(Table1[Key])

VAR Table2 = SUMMARIZECOLUMNS(Table2[Key])

RETURN EXCEPT(Table1, Table2)

otherwise switch the tables around in the EXCEPT.

This will give you eveything in the first table which is NOT in the second table.

Hope that helps.  Please remember to mark as solved if so.

[Gurpreetsingh1]

Hi @m4ni Thanks for the help, but i need both the matched & unmatched orders in same table along with other columns.

Eg Desired Result 

Sales Order Product Code (SAP) Amount (SAP) Product Code (SF) Amount (SF) Match Type

SO101	PRD001	5,000	PRD001	5,000	✅ Full Match
SO101	PRD002	3,200	PRD008	3,000	❌ Product Code Mismatch
SO102	PRD003	2,500	PRD009	2,400	❌ Product Code Mismatch
SO103	PRD004	4,000	PRD004	4,100	⚠️ Amount Mismatch
SO104	PRD005	1,800	—	—	❌ Missing in SF
SO104	PRD006	2,200	PRD006	2,200	✅ Full Match
SO105	—	—	PRD007	2,800	❌ Missing in SAP

[V-yubandi-msft]

Hi @Gurpreetsingh1 ,

Thank you for reaching out to the fabric community.  We have reviewed your scenario and implemented the logic to classify the records based on Product Code and Amount comparisons between the Salesforce and SAP tables.

FYI:

Based on our testing, the logic is working as expected. Please refer to the  Match Type column in the result to view the classification. 

For better understanding, I’ve attached the PBIX file. Kindly go through it, it demonstrates the logic and output based on your scenario.

Thank you for your response, @m4ni & @Deku .

If my message helped solve your issue, please mark it as Accepted Solution. If it was helpful, consider giving it a Kudos.

[Deku]

I would union them. Then group by all three columns, and count the number of rows. Those with a count of 2 are the same between the two sources. Those with a 1 only exist in one source or the amount differences between sources.