Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

Many-to-Many relationship with a twist

I am evaluating parts on leased aircraft and have to compare the condition of the part initially when the aircraft is delivered to an operator, to the condition of the part when it is returned back to the lessor (the aircraft is leased into the operator for a period of years). Naturally what happens during the aircraft's operations various parts are removed and replaced with approved parts that may not always have the same part number. The new part is may be a modified/improved part, and as a result the manufacturer assigns a replacement part number is different than the original part number. Or the replacement part may just be manufactured by a differnet part manufacturer and that manufacturer also has a different part number (many identical parts are manufactured by various manufacturers). 


So from a database perspective I have three separate tables to deal with; 

1) the part status table at the original lease delivery

2) the part status table at lease return

3) a replacement parts listing table identifying all the original part numbers and potential replacement part numbers


What I initially need to do is to first identify the parts that were on the aircraft at the original delivery but are no longer on the aircraft at leaset return. I do that by performing a Left Anti Join between the original lease delivery table and the lease return table. I then perform a Right Anti Join on the same two tables to identify the replacement parts that are now on the aircraft that do not match the delivery numbers. That then gives me two "orphan tables" that identify parts than do not match from Delivery to Return. 


The next procedure, though, is the tough one which is somehow matching up the original part numbers with the new part numbers and then comparing the condition of the part from delivery to retuen. It would be relatively easy if there was only one new part number for each old part number (one-to-one or even one-to-many), but the issue is that there could be multiple new part numbers that could replace a singe old part number, and there could be multiple old part numbers that a single new part number could replace (in other words a many-to-many relationship).


The final step is that I need to match up the old part numbers to the new part numbers and then perfom an analysis on the parts to determine whehter or not the returned part is in better or worse conditon as the original delivery part (and calculate out a value for the difference).  In other words I need to relate a specific original delivery part number/serial number to a redelivery part number/serial number. The reality is that what I do there is to basically replace the unique serial numbers for each part with some other unique number that I can then easily use to match up the two parts (old and new). The way I'm handling that is to actually rank the parts by their condition and then use the "Part Number/Rank" of the old part to the "Part Number/Rank" to the new part. That then allows me to match up individual parts and compare conditions. 


I have to assume that this is not a unique case and must somehow pop up in other data analysis activies. I realize that this is a Many-to-Many relationship issue so maybe I'm just buried too far into the weeds to see a simple solution.


Helpful resources


Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.