cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Zaibass81 Frequent Visitor
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:

2019-11-13_17h43_01.png 

1 ACCEPTED SOLUTION

Accepted Solutions
jborro Member
Member

Re: BOM Extended Quantity

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

 

View solution in original post

2 REPLIES 2
jborro Member
Member

Re: BOM Extended Quantity

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

 

View solution in original post

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,665)