Ignore duplicate records from calculated column - 1 to 1 relationship

[Jebilaya]

I have two tables that contain the same order number and delivery date which I need to be able to have a 1-1 relationship.  I have  created a (what I thought would be) unique field in each table by creating a column at the end of each table concatenating the order number and delivery date.

As you can see from screenshot below the vast majority of records are unique, but there are cirumstances where the same order can be delivered on the same date.  As these are so few and far between I want to just ignore these records from the table so I can create the 1-1 relatinoship without having to check data each week more is added.

As it's a calculated column I don't seem to be able to do this in power query.  Any ideas?

[NickA01 · Accepted solution]

I'd start by creating a new page, 
Add a slicer and pull in the LookupRefInv from Table A
Next add a table and pull in all the columns from Table A. 
Using the slicer, select one of your duplicate values from the above screenshot.
This should now give you x number of rows and you should hopefully see what is causing the duplication. 

Duplicate this process for the second table. 

Once you now what is causing the duplication, then look at how to resolve. 
If the columns causing the dups are not needed, remove. 
If it's a case of you have multiple enteries at different times during the same day but you only need the final Invoise details then try doing a Rank operation on the time and select the highest value. 
See this Post https://community.powerbi.com/t5/Desktop/How-to-do-rank-partition-like-SQL-in-DAX/td-p/2139662
Or https://dax.guide/rankx/

The Nick

If the post helps please give a thumbs up || If it solves your issue, please accept it as the solution to help the other members find it more quickly.

If it's the biggest heap of stinky smelly stuff then I'm sorry .

[amitchandak]

@Jebilaya , If they are 1-1 Relationship, They can be merged together. Or Make it one to many based on what you treat as master