turning single column into multiple rows based on text values for filtering based on BOM combination

My reply was too quick I did not understand your problem :smileyvery-happy:

Okay so you have your 3 tables in the query editor. 

The one that's interesting to us is Production BOM Line, we won't touch the others. I'll assume that columns type is text, otherwise just change the type to text before applying my changes.

Here is the Query :

let
    Source = Any,
    #"Duplicate" = Table.DuplicateColumn(Source, "Item", "ItemType"),
    #"Split" = Table.TransformColumns(Duplicate, {{"ItemType", each Text.Start(_, 2), type text}}),
    #"Pivot" = Table.Pivot(Split, List.Distinct(Split[ItemType]), "ItemType", "Item"),
    #"Rename" = Table.RenameColumns(Pivot,{{"SD", "Dough"}, {"SF", "Filling"}, {"ST", "Topping"}}),
    #"AddDough" = Table.NestedJoin(#"Rename", {"Dough"}, Item, {"Item"}, "Item", JoinKind.LeftOuter),
    #"DoughResult" = Table.ExpandTableColumn(#"AddDough", "Item", {"Description", "Equipment"}, {"Dough.Description", "Dough.Equipment"}),
    #"AddFilling" = Table.NestedJoin(#"DoughResult", {"Filling"}, Item, {"Item"}, "Item", JoinKind.LeftOuter),
    #"FillingResult" = Table.ExpandTableColumn(#"AddFilling", "Item", {"Description", "Equipment"}, {"Filling.Description", "Filling.Equipment"}),
    #"AddTopping" = Table.NestedJoin(#"FillingResult", {"Topping"}, Item, {"Item"}, "Item", JoinKind.LeftOuter),
    #"ToppingResult" = Table.ExpandTableColumn(#"AddTopping", "Item", {"Description", "Equipment"}, {"Topping.Description", "Topping.Equipment"})
in
    #"ToppingResult"

Here is the result : 

Here is a step by step explaination so you can understand what we are doing :

1. Duplicate column Item

2. Exctract the two first characters from the duplicated column

3. Select the duplicated column again and then in the Transform Ribbon choose Pivot column

The following menu will open, in Values Column select Item and in Aggregate Value Function select Do not aggregate (or something similar). Then hit OK

4. Rename the 3 new columns Dough, Filling and Topping.

5. We will now add Description and Equipment for each type : go to Merge Queries

select Dough column from Production BOM Line and Item column from table Item, and let Left Outer as Join kind

6. Develop the new column

7. Repeat operations 5 and 6 for Filling and Topping. 

I hope this solution will work for you, tell me if there is any problem

Regards, 

Etienne