Match tables based on multiple columns

[rhayward2209]

Hi There,

Im trying to match two tables for reconciliation purposes. These tables show the hours worked based in our system and the clients system and are often different.

Im looking for a way to compare the two tables and return a value when they do not match.

Date	Employee	Hours	Work Order
1-Jan	123456789	1.6	WO001
1-Jan	123456789	1.5	WO002
1-Jan	123456789	1.5	WO003
1-Jan	123456789	1.9	WO004
1-Jan	123456789	2	WO005
		8.5

Date	Employee	Hours	Work Order
1-Jan	123456789	1.5	WO002
1-Jan	123456789	1.5	WO003
1-Jan	123456789	1.9	WO004
1-Jan	123456789	2	WO005
		6.9

For instance in this scenario I can visually see that the difference is 1.6 hours and that from table 1 there is WO001 that is not present in the second table.

I would like a column added to the first table that would say "Match" or something similar for WO002 - WO005 and "No Match" for WO001

Ive done some calculated columns for matching one column to another but the second column is stumping me.

[rhayward2209]

@amitchandak 

Thanks so much for your help! The latest measure that you mentioned works for about 70% of cases but it seems to struggle on certain matches when theres obiously a duplicate (for example, Table 1 has a work order for 5 hours listed twice, and table 2 has it listed once and the measure returns that neither of them match since it has doubled the hours, when ideally it would show that one of them matches but the second one doesnt.

I hope that makes sense.

[amitchandak]

@rhayward2209 , You can have a column like this too, this will give grouped data

sumx(filter(Table1, Table1[Date] = earlier(table1[Date]) && Table1[Employee] = earlier(table1[Employee]) && Table1[Work Order] = earlier(table1[Work Order]) ), Table1[Hours])

You can diff between the last suggested measure and this one and create a flag.

Another way is have common date, employee and Work order dimensions and create to measures

hour m1 = sum(Table1[hours])

hour m2 = sum(Table2[hours])

a final meausre like this to give only rows where [hour m1] > [hour m2]

if([hour m1] > [hour m2], [hour m1] -[hour m2] , blank())

or [hour m1] < [hour m2]

if([hour m1] < [hour m2], [hour m2]  -[hour m1] , blank())

[rhayward2209]

@amitchandak 

Please disregard the earlier post as I have figured out that the issue is due to an increased granularity in the data from Table 1 that Table 2 does not have.

For example, WO005 could be 2 hours, but could consist of two tasks of 1.5 and 0.5 (lets call these Job #).

So when the function sees these 2 jobs # it matches the total time to both of them. As per below,

Date	Employee	Hours	Work Order	Job #	Hourst2
1-Jan	123456789	1.6	WO001	1	0
1-Jan	123456789	1.5	WO002	1	1.5
1-Jan	123456789	1.5	WO003	1	1.5
1-Jan	123456789	1.9	WO004	1	1.9
1-Jan	123456789	1.5	WO005	1	2
1-Jan	123456789	0.5	WO005	2	2
		8.5

Date	Employee	Hours	Work Order
1-Jan	123456789	1.5	WO002
1-Jan	123456789	1.5	WO003
1-Jan	123456789	1.9	WO004
1-Jan	123456789	2	WO005
		6.9

See how the Matched hours themselves are technically correct but it puts the 2 total two hours for both rows of the work order.

The second table does not have the Job #'s whic may cause an issue.

[rhayward2209]

Thank you for your reply,

Ive tried this column and it works most of the time however some of the Hourst2 values are adding togetrher and im unsure as to why. They have different work order numbers but are still adding together for some reason.

Date	Employee	Hours	Work Order	Hourst2
1-Jan	123456789	1.6	WO001
1-Jan	123456789	1.5	WO002	3.00
1-Jan	123456789	1.5	WO003	1.50
1-Jan	123456789	1.9	WO004	1.90
1-Jan	123456789	2	WO005	2.00
		8.5		8.40

This is what its doing, any idea as to why this is occuring and how to fix it?

[amitchandak]

@rhayward2209 , Create a new column like this in Table1. You can add remove filters based on need

Hours t2 = sumx(filter(Table2, Table2[Date] = table1[Date] && Table2[Employee] = table1[Employee] && Table2[Work Order] = table1[Work Order] ), Table2[Hours])

Now you can compare Hours t2 and Hours in table1 and create the flag or any other calculation.

[ryan_mayu]

@rhayward2209 

you can try to create a new table

Table = except(Sheet1,Sheet2)