Function to identify 'Path' of Superseded Items

jilbobagins

Member
Joined
Apr 11, 2019
Messages
80
Reaction score
0
Points
6
Excel Version(s)
2016
Hi All,

Please refer tables in attachment

I have a Table named "Changed" 'Blue' which details part number and the part number they've changed to. Sometimes there can only be one occurrence of a part changing, but on occasion that part its changed to also changes!

What im after from this is a method to identify by part what the latest possible part number it has changed to, how many times has it been through a change and the string of those change details (orange table example)

Im feeling its a List.Accumulate...but that beyond me .....maybe its easier than it looks??

Thanks
 

Attachments

  • Superseeded Part.xlsx
    12.2 KB · Views: 7
  • Capture.jpg
    Capture.jpg
    98 KB · Views: 8
Well, you've had over 100 views of your thread, so I guess so ... ;)

Let me have a look and see if I can offer any wisdom.

EDIT: No - beyond me, sorry. Not even sure I understand what you are trying to do. Maybe you need to provide a more detailed explanation?
 
You can have any length of change chain that needs to be investigated to get a complete history. I have no idea how this could be done with Power Query.

If I needed it for myself, I would try to create the evaluation with a VBA macro, even though I don't have much experience in VBA.
 
Im sure it can be done, HorseyRide developed a fantastic piece of M utilizing the List. Accumulate function to create a Multi Level BOM Path for me.....I don't think he lives on the forum anymore though. Shame
 
I tend to agree with pinarello, although Power Query could very probably do it with some of its iterative/recursive functions I haven't got that far with PQ so far. In the attached is a vba solution. It uses a bit of recursion. It's not neat and tidy, I just kept experimenting until it worked. Click the button at cell H17 to fill in data from cell K17. From K31 there's another table to compare the results with your expected results. Nearly all are TRUE meaning an exact match. Where they're different is where I think you've got it wrong (but put me right if I'm wrong!)

Important: Note that this could go into everlasting iterations if your data is not good, in that there should only be unique part numbers in Part New and Part Old columns (a single occurrence of each) and there is no attention at all paid to dates being in the correct order. I realise that people assigning new part nos. might actually issue old and no longer used part numbers. I haven't coped with that - it would involve a fair bit more coding!
 

Attachments

  • ExcelGuru11308Superseeded Part.xlsm
    29.7 KB · Views: 9
Last edited:
Thanks for looking at this P45cal, i'll have a play and see how I get on. Similar to you I've not managed to crack the really special functions in PQ yet...I need too though
 
I forgot to delete columns G & H, they're not needed at all.
 
Back
Top