let // Function to retrieve all children per "non-purchase-item"-row in report-table fnAllChildrenWithQties = (Partition) => let // Adding a column with a table that contains all rows where the current row ("Earlier") is a parent to (or: "Where this row is part of any of the level-columns of the classical hierarchy") #"Added Custom" = Table.AddColumn(Partition, "AllChildren", (Earlier) => Table.SelectRows(Partition, each (Text.Contains([PathExplodeCode], Earlier[PathExplodeCode])) and [PurchaseItem]<>null)), #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"PathExplodeCode", "AllChildren"}), #"Expanded AllChildren" = Table.ExpandTableColumn(#"Removed Other Columns1", "AllChildren", {"ComponentID", "TotalQty"}) in #"Expanded AllChildren", // Reference BOMReport-table Source = BOMReport, // Cleanup columns #"Removed Other Columns" = Table.SelectColumns(Source,{"ComponentID", "TotalQty", "PathExplodeCode", "TopParentProduct", "PurchaseItem"}), // Group on TopParentProduct for performance reasons: Creating a partitioned table GroupOnTopParentProduct = Table.Group(#"Removed Other Columns", {"TopParentProduct"}, {{"All", each _, type table}}), // executing the function is faster on the partitions because they have less rows AllChildren = Table.AddColumn(GroupOnTopParentProduct, "e", each fnAllChildrenWithQties([All])), // Cleanup columns again #"Removed Other Columns1" = Table.SelectColumns(AllChildren,{"e"}), // Expand result of function #"Expanded e" = Table.ExpandTableColumn(#"Removed Other Columns1", "e", {"PathExplodeCode", "ComponentID", "TotalQty"}, {"PathExplodeCode", "ComponentID", "TotalQty"}), // Assigning number-formats to columns #"Changed Type" = Table.TransformColumnTypes(#"Expanded e",{{"TotalQty", type number}}) in #"Changed Type"