Generate Recursive Bill of Material

[tjgagner]

I have a table imported into PowerBI from that has the relation between a top-level Bill-of-Material part number "BOM Material" and child part numbers "Component Material".  The challenge I am having is that child "Component" part numbers themselves might be "BOM materials" I would like to see a flattened version of the Bill of Material with all component part numbers and the different levels represented.

The approach I am thinking about taking is populating a New Table with a query from the original, which includes recursion to get to a flat BOM like shown below.

How can I write a query that does this type of recursion?

[lbendlin · Accepted solution]

Parent-Child Hierarchies with multiple parents in Power BI with Power Query

[v-fenling-msft · Accepted solution]

Thanks for lbendlin's concern about this issue.

Hi, @tjgagner 

I am glad to help you.

Perhaps you can refer to my DAX to create a calculation table:

FlattenedBOM =
UNION (
    SELECTCOLUMNS (
        FILTER (
            'OriginalTable',
            'OriginalTable'[BOM Material]
                IN SELECTCOLUMNS ( 'OriginalTable', 'OriginalTable'[Component Material] )
                    = FALSE ()
        ),
        "BOM Material", [BOM Material],
        "Component Material", [Component Material],
        "Level", 1
    ),
    ADDCOLUMNS (
        SELECTCOLUMNS (
            FILTER (
                CROSSJOIN (
                    'OriginalTable',
                    SELECTCOLUMNS (
                        'OriginalTable',
                        "BOM Material2", 'OriginalTable'[BOM Material],
                        "Component Material2", 'OriginalTable'[Component Material]
                    )
                ),
                [BOM Material2] = [Component Material]
            ),
            [BOM Material],
            "Component Material", [Component Material2]
        ),
        "Level", 2
    )
)

Result:

I have attached the pbix for this example below, I hope it helps!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tjgagner]

Thank you for your time answering my question. I did try and use this query but hung up on the calculation step ("working on it"). I will come back to it but am going to try the @lbendlin referenced solution as well.

[lbendlin · Accepted solution]

Parent-Child Hierarchies with multiple parents in Power BI with Power Query