Forum Discussion
Expanding Bill of materials to get full product list
Hi all,
I have 3 table,
Table 1 - Product master
| Item Type | Item List | Item Code |
| Item | Mouse | MS001 |
| Item | KeyBoard | MS002 |
| Item | Laptop | MS003 |
| Item | Notebook | MS004 |
| Item | Pen | MS005 |
| Item | Water bottle | MS006 |
| Item | Coffee Mug | MS007 |
| BOM | Starter Pack | MS008 |
I have the Bill of material list
Table 2 - BOM Master
| BOM-Parent | Component Product | Qty in BOM |
| Starter pack | Notebook | 1 |
| Starter pack | Pen | 1 |
| Starter pack | Water bottle | 1 |
| Starter pack | Coffee Mug | 1 |
Table 3 - Order Master
| Order items | Item Code | Qty |
| Mouse | MS001 | 6 |
| Keyboard | MS002 | 6 |
| Laptop | MS003 | 6 |
| Starter Pack | MS008 | 6 |
I want to create a view or table which will look up table 2, if an Item name exists as BOM-Parent, I want to expand it into its BOM-Child in a master order table just that it looks like this
| Order Items | Item Code | Qty |
| Mouse | MS001 | 6 |
| Keyboard | MS002 | 6 |
| Laptop | MS003 | 6 |
| Notebook | MS004 | 6 |
| Pen | MS005 | 6 |
| Water bottle | MS006 | 6 |
| Coffee Mug | MS007 | 6 |
Where the Starter Pack is split into its components.
Has anyone done something like this before and how did you do it?
1 Reply
- You may try PowerQuery.
