Expanding Bill of materials to get full product list

New Contributor

Hi all,
I have 3 table, 
Table 1 - Product master 

Item TypeItem ListItem Code
ItemMouseMS001
ItemKeyBoardMS002
ItemLaptopMS003
ItemNotebookMS004
ItemPenMS005
ItemWater bottleMS006
ItemCoffee MugMS007
BOMStarter PackMS008


I have the Bill of material list 

Table 2 - BOM Master

BOM-ParentComponent ProductQty in BOM
Starter packNotebook1
Starter packPen1
Starter packWater bottle1
Starter packCoffee Mug1

 

Table 3 - Order Master

Order itemsItem CodeQty
MouseMS0016
KeyboardMS0026
LaptopMS0036
Starter PackMS0086

 

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 ItemsItem CodeQty
MouseMS0016
KeyboardMS0026
LaptopMS0036
NotebookMS0046
PenMS0056
Water bottleMS0066
Coffee MugMS0076

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.