Summarizing a hierarchical Bill of Materials

NanditaPatil

We export Bill of Materials which we use to generate Purchasing information. Not all the items are “Purchasable” as they may be pre-assembled by our vendors. Items are listed multiple times and with different quantities depending on the sub assembly they belong to. Anyways, I’m looking to sort that can summarize the data to make a “Purchase List” for the full device. A pivot table would be the obvious choice, but it doesn’t take into account the hierarchy of BOM structure. So sub components of an assembly are only counted once in total when there is a possibility the sub assembly could be used multiples times in the same device.

The first column of the table shows a new decimal point for each change in hierarchy:

1. Assembly

-> 1.1. Sub Assembly

->->1.1.1. Component A

-> 1.2. Component B

…etc.

BOM 120-08-001 COMPLETE ASSY_Alteryx Community.xlsx

KGT

I've read that a couple times and still not sure I've understood. Is every whole number a new component that you're concerned about?

Bring the data in (may need to re-assign headers or start data import on line 2.

Text to Columns will separate [m] on a full stop.

Then you can aggregate on the different columns.

So, to get a purchase list for Item1, you can then filter on [m1]="1" and summarise on the parts to give the list of total parts for the item.