Expending/ un expending BOM stracture

[TomerIwanir1]

Hi all,

Attached a pic , on the left I have a table (in reality its in PBI with 150K rows)

trying to transform it into the right table but I have no idea how to start, any ideas ?

the right QTY need to be multyply but I left it W/O "=" in order to show the calc

thx

[v-dineshya · Accepted solution]

Hi @TomerIwanir1 ,

Thank you for reaching out to the Microsoft Community Forum.

Power Query doesn't allow recursive functions to be defined directly inside a query step. You must define the function as a separate query first, then call it from your main query.

Please follow below steps.

1. Create a New Blank Query (ExpandBOM) for the Recursive Function in Query editor, with below M code.

(rootParent as text, currentParent as text, currentQty as number, table as table) as list =>
let
children = Table.SelectRows(table, each [Parent] = currentParent),
results = List.Combine(
List.Transform(children[Child], (child) =>
let
childRow = Table.SelectRows(children, each [Child] = child){0},
childQty = childRow[QTY],
cumulativeQty = currentQty * childQty,
childResults = @ExpandBOM(rootParent, child, cumulativeQty, table)
in
if List.IsEmpty(childResults) then
{ [Parent = rootParent, Child = child, QTY = cumulativeQty] }
else
childResults
)
)
in
results

2. Create a New Blank Query (BOM) in Advanced editor  with below M code, to Call the Function.

let
Source = Table.FromRows({
{"A", "B", 2},
{"A", "C", 5},
{"B", "D", 3},
{"B", "E", 6},
{"C", "F", 4},
{"D", "T", 8}
}, {"Parent", "Child", "QTY"}),

ChangedTypes = Table.TransformColumnTypes(Source, {{"Parent", type text}, {"Child", type text}, {"QTY", Int64.Type}}),

AllParents = List.Distinct(ChangedTypes[Parent]),
AllChildren = List.Distinct(ChangedTypes[Child]),
RootParents = List.Difference(AllParents, AllChildren),

AllPaths = List.Combine(
List.Transform(RootParents, each ExpandBOM(_, _, 1, ChangedTypes))
),

ResultTable = Table.FromRecords(AllPaths)
in
ResultTable

3. Please refer Output snaps and PBIX file for your reference.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-dineshya]

Hi @TomerIwanir1 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[techies]

Hi @TomerIwanir1 in case you want to do with PATH function in DAX, let me know

[speedramps]

@techies 

Why haven't you posted how? 🤔🤔🤔

[techies]

Hi @speedramps thanks for checking 🙂 just added the screenshot for reference in case it helps

[DataNinja777]

Hi @TomerIwanir1 ,

To flatten and calculate the full quantity path in a recursive Bill of Materials structure using Power Query, you can use self-joins and step-by-step expansion. Assuming your table is named BOM with columns Parent, Child, and QTY, start by duplicating the table and merging it back to itself to go one level deeper. For example, merge BOM with itself where BOM[Child] = BOM2[Parent]. After merging, expand the new columns (e.g., Child2, QTY2) and multiply QTY * QTY2. Then repeat the process: merge the resulting table to BOM again using Child2 = Parent, expand to get Child3, QTY3, and multiply by the existing quantity chain. Repeat until you reach the maximum known depth or leaf nodes.

Here’s a simplified example in M code to illustrate going three levels deep:

let
    Source = Excel.CurrentWorkbook(){[Name="BOM"]}[Content],
    Level1 = Table.RenameColumns(Source,{{"Parent", "Parent1"}, {"Child", "Child1"}, {"QTY", "QTY1"}}),

    Merge1 = Table.NestedJoin(Level1, {"Child1"}, Level1, {"Parent1"}, "Next1", JoinKind.LeftOuter),
    Expand1 = Table.ExpandTableColumn(Merge1, "Next1", {"Child1", "QTY1"}, {"Child2", "QTY2"}),
    AddQTY2 = Table.AddColumn(Expand1, "TotalQTY2", each [QTY1] * [QTY2]),

    Merge2 = Table.NestedJoin(AddQTY2, {"Child2"}, Level1, {"Parent1"}, "Next2", JoinKind.LeftOuter),
    Expand2 = Table.ExpandTableColumn(Merge2, "Next2", {"Child1", "QTY1"}, {"Child3", "QTY3"}),
    AddQTY3 = Table.AddColumn(Expand2, "TotalQTY3", each [TotalQTY2] * [QTY3])
in
    AddQTY3

To finish, filter the rows where the latest Child (e.g., Child3) does not appear in any Parent column in the original data. This indicates leaf nodes like "T" or "F". You now have the full path and total quantity as a result of all multiplications along that path.

Best regards,