Compare two lists employee name as connector. New and lost

[Ric123]

Have 2 tables in PowerBI. They are 2 CSV files in SPO. 

Each table has a column (text) called: EmpName ( last name, First and middle, military rank) 

DOE, JOHN B O4

Table A has only employees currently on onboarded 

table B has onboarded and also future employees. This list contains duplicate id numbers called billets. 

treid to make a connection using the employees names between the two columns but for some reason all those employees that have a military rank, are not connected. All the others not military are. What could be the cause? Maybe some hidden text or invisible space that prevents the connection?

at the same time tried to separate the columns divided by separator, ( so by last name) without any luck. 

my end goal is to get my onboarded personnel from list A verifying both reports and on a separate visual see who is coming onboard from table B keeping in mind that on table B I have duplicate id numbers called billets. 

thank you very much for your help! I have tried and retried with no luck and that is why I decided to post this here. 

[Omid_Motamedise]

Can you provide an example of your data here?

[PwerQueryKees]

I hate it when I need to match on text values. Power Query is case sensitive, hidden spaces, etc.
What you can try is to use Merge with fuzzy matching.
Start with the following selection of parameters:

Similarty 1 = exact match

Ignore case speaks for itself

And match by combining text parts basicall ignores spaces.

Click the little i's for more details

If the match still not works, lower the similarity parsmeter

Hope this works BUT matching on this type of data especially if the data originates from 2 different sources or systems, will always be unreliable and give you unpleasant surpises...

Good luck!!