Compare two tables and include rows that don't match

[mbpaul]

Hello, I am fairly new to PowerBi and I am feeling like this should be a fairly easy answer but for some reason I can't wrap my head around the logic to get it to display the way that I want. I have two tables that I am trying to do a comparison of hours from our two time keeping systems to ensure they are reconcilled. 

Table 1

Equipment	Job	Date	Hours
AC017	Sample	2021-05-17	10
AC017	Sample	2021-05-18	8
AC017	Sample	2021-05-20	6
AC025	Sample	2021-05-17	10
AC025	Sample	2021-05-20	10

Table 2

ProjectCode	WorkDate	ResourceID	Hours
Sample	2021-05-17	AC017	7
Sample	2021-05-18	AC017	10
Sample	2021-05-19	AC17	10
Sample	2021-05-20	AC017	6
Sample	2021-05-17	AC025	10
Sample	2021-05-20	AC025	10

Desired Output:

Slicer: Date range

Slicer: Project

Slicer: Equipment Code

Date	Equipment Code	Table 1 Hours	Table 2 Hours	Difference
2021-05-17	AC017	10	7	3
2021-05-18	AC017	8	10	2
2021-05-19	AC17		10	10
2021-05-20	AC017	6	6	0
2021-05-17	AC025	10	10	0
2021-05-20	AC025	10	10	0

I have tried a number of things to get it to work but I can't seem to get it to do just what I want. I tried to create a merged column "Date-Equip-Job" in both tables and use that as a relationship but it only brings in matching rows and not rows that may be in one table and not the other (for example, row 3 in desired (AC17) would be excluded). 

I have also tried using a calendar table to filter but then the tables do not link together properly and I get hours from the same date but not the same unit on the line item and I can't seem to create a relationship due to ambiguity.

This seems like it should be a really easy thing to do but it has me stumped and I haven't been able to find a forum post that has helped.

[Fowmy · Accepted solution]

@mbpaul 

Simple merging in PQ should work for you if I understood your issue correctly. I attached the file.
You also try the Fuzzy Merge option if you want to merge based on partial matching.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

 

[Fowmy · Accepted solution]

@mbpaul 

Simple merging in PQ should work for you if I understood your issue correctly. I attached the file.
You also try the Fuzzy Merge option if you want to merge based on partial matching.

[Greg_Deckler]

@mbpaul Not sure I am following this entirely but seems to me that you want an inverse selector or a complex selector:

Inverse Selector - Microsoft Power BI Community

The Complex Selector - Microsoft Power BI Community

[mbpaul]

Hi Greg,

Thanks for the quick response. I am not overly concerned about the selectors as I am using them just as basic filters and ultimately I want to be able to show all data. My big issue is just getting the data to relate properly so it displays all the information. I have no problem comparing the rows in the 2 tables if the equipment number is the same in both tables (Desired outputs: row 1,2,4,5,6). My issue is getting it to display if the equipment number does not show up in both tables for example in 'Desired outputs: row 3'. The unit number was entered as AC17 instead of AC017 in table 2. I get rows 1,2,4,5,6 in my visual but I can't seem to get row 3.  

I hope that makes sense.

Michael