How can I sort a multilevel BOM?

Question

I have a Bill of Materials that looks like this inside of it

But for various reasons my ERP will only allow me to export the BOM as an .xlsx that looks like this

Where each member item is tied to the member item above. These means when I make a pivot table and try to find the member items of just my product, it'll only give me Assembly 1 and 3 and then I have to do another pivot table to find 1 and 3 and so forth. Some of my BOM's are 6 levels deep and making a table for each sub assembly is not ideal. Anyone have any ideas of how to handle this? My thoughts are to use something like pandas and script it out, but I wasn't sure if there was a method inside Excel that I was missing that might cover this. My end goal is to recreate something like the first picture, although the nesting isn't necessary.