Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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
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
Thank you Sir, it did worked well, however as you mentioned is runing wery slow when applied to a sizable table
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |