cancel
Showing results for
Did you mean:
Highlighted
Zaibass81 Frequent Visitor

## BOM Extended Quantity

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: 1 ACCEPTED SOLUTION

Accepted Solutions
jborro Member

## Re: BOM Extended Quantity

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}}),

in

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

2 REPLIES 2
jborro Member

## Re: BOM Extended Quantity

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}}),

in

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 Frequent Visitor

## Re: BOM Extended Quantity

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

Announcements   