BOM Extended Quantity

[Zaibass81]

Hi All,

I am new to this group 🙂

i am looking for sulution to built M code (preferable ) or DAX to get BOM extended Qty, please see example attached:

 

[Anonymous · Accepted solution]

Hi @Zaibass81 

as far as I understand what the LOOKUP does in your Excel example, this is the code below:

Main table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiAz0jQ6VYnWglIwjfCJkDlESRMwHzjCE8iDYTqEpMGRycWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Level = _t, Part = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"Part", type text}, {"Quantity", type number}}),
    
    
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", (r) => if r[Level] = 1 then r[Quantity] else r[Quantity] * fBOM(#"Added Index", r))
in
    #"Added Custom"

fBOM function:

(pTable as table, r as record)=>
    let
        LookUp = Table.LastN(Table.SelectRows(pTable , each [Index] < r[Index] and [Level] = r[Level]-1), 1),
        Output = if LookUp[Level]{0} = 1 then LookUp[Quantity]{0} else LookUp[Quantity]{0} * fBOM(pTable, LookUp{0})
    in 
        Output

Just be aware, this is a brutal-force approach and may run slow when applied to a sizable (few thousand rows) table.

Kind regards,

JB

[Zaibass81]

Thank you Sir, it did worked well, however as you mentioned is runing wery slow when applied to a sizable table