BOM树形结构分解

题目:


BOM(Bill of Material)即物料清单,用于辅助企业生产管理,要求根据父件需求数逐级分解为子件数量。
比如生产1个a需要3个b,生产1个b需要4个c,生产1个c需要2个d,现需要生产5个a问需要多少个d?

解法:

以上面abcd为例,按照常规思维,我要知道5个a需要多少个d,那显然应该从第一级依次往下一步步分解。
现在共4个层级,先找出第一级a,然后根据a算b,再根据b算c,再根据c算d,整个过程我用4个字来形容叫做"顺藤摸瓜"。
那么回到题目中,第一步肯定还是先要筛选出第一层级,筛选[父件名称]列包含"产品"的行即可,然后新增一列[path]用于记录分解过程。
这样一级就找到了,下面开始找二级,哪些是二级的呢?这一步至关重要,这里理解了后面都好理解。
二级的条件就是,既属于刚才筛选出的一级表中的[子件型号],又属于源数据表中的[父件型号],必须是上有老下还有小,也就是两列的交集,可以通过合并查询中的"内部"连接方式筛选出来。
每筛选完一个层级,就把之前的分解过程记录在[path]中,然后继续找下一级,一直重复下去。
但是这么无限重复下去何时是尽头呢?题目中最大层级深度为6,当到了第六级,此时[子件型号]全部都是原料,源数据中没有任何行的父件是原料,所以如果再筛选第七级得到的是个空表,于是停止重复。
最后把每一级的筛选表合并,再根据[path]中的记录即可得到如图所示的分解过程和分解数量。

题目是做出来了,但显然这么做太傻了,如果层级有几十上百个,那得写很长很长。
上面逐级查找下一级的过程,都是机械而重复的,所以可以用一个循环来代替,循环终止的条件是合并表后行数为0,那就是while循环,也就是List.Generate

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    筛选一级 = Table.SelectRows(源, each Text.Contains([父件名称], "产品")),
    构建table = Table.AddColumn(筛选一级, "path", each #table({"型号","数量"},{{[父件型号],[数量]}})),
    循环 = Table.Combine(List.Generate(()=>
             构建table,     
             each Table.RowCount(_) > 0,
             each [ 
                  a = Table.NestedJoin(源,{"父件型号"},_,{"子件型号"},"a",JoinKind.Inner),
                  b = Table.ExpandTableColumn(a, "a", {"path"}, {"上一级path"}),
                  c = Table.AddColumn(b,"path", each Table.Combine({[上一级path], #table({"型号", "数量"}, {{[子件型号], [数量]}})}))
                  ][c]
     )),
    分解过程 = Table.AddColumn(循环, "分解过程", each Text.Combine([path][型号],"->")),
    分解数量 = Table.AddColumn(分解过程, "分解数量", each List.Product([path][数量])),
    删除多余列 = Table.RemoveColumns(分解数量,{"path", "上一级path"})
in
    删除多余列

在附件中我用笨方法把每一级拆解下来,方便对照着一步步理解,具体看附件吧。

附件

13 Replies to “BOM树形结构分解”

  1. 博主有这样的水准,无偿帮助国人,敬佩。这个博客会成为众多爱好者的启蒙工具,我们会记住的。
    本文,用2列描述多层的BOM表,对我们来说更需要分解为多列(每列一层),这样更方便利用吧,如果方便,还请不吝赐教!

  2. 个人理解:有了path和数量,实际工作中的使用就是找到最小采购单元,分组求和进行采购了

  3. 万分感激,学习了大半年终于把这个看懂,也解决了实际工作中的许多问题,感觉PQ真的很强大,还需要更加深入的去学习。

  4. 读懂了,作者太强大了!

    目前这套做法有个场景无法覆盖到:
    不是所有的材料都处于相同的阶层,比如以电脑制造为例
    lvl1 - 电脑
    lvl2 - 硬盘
    主板
    lv3 - IC
    电阻
    电容等
    用上述做法,取得最终的物料清单只有lv3的 IC/电阻/电容等, lv2的硬盘已经没有再往下展开了。
    所以使用目前的 以组的lvl来累积表格的需要调整。
    水平有限,目前只能提出问题,还没法解决问题 🙂

  5. 构建table这行可以稍微改一下,把BJ这一级也显示出来
    let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    筛选一级 = Table.SelectRows(源, each Text.Contains([父件名称], "产品")),
    构建table = Table.AddColumn(筛选一级, "path", each Table.Combine({#table({"型号","数量"},{{[父件型号],1}}), #table({"型号", "数量"}, {{[子件型号], [数量]}})})),
    循环 = Table.Combine(List.Generate(()=>
    构建table,
    each Table.RowCount(_) > 0,
    each [
    a = Table.NestedJoin(源,{"父件型号"},_,{"子件型号"},"a",JoinKind.Inner),
    b = Table.ExpandTableColumn(a, "a", {"path"}, {"上一级path"}),
    c = Table.AddColumn(b,"path", each Table.Combine({[上一级path], #table({"型号", "数量"}, {{[子件型号], [数量]}})}))
    ][c]
    )),
    分解过程 = Table.AddColumn(循环, "分解过程", each Text.Combine([path][型号],"->")),
    分解数量 = Table.AddColumn(分解过程, "分解数量", each List.Product([path][数量])),
    删除多余列 = Table.RemoveColumns(分解数量,{"path", "上一级path"})
    in
    删除多余列

  6. 博主 您好
    我在套用的你的案例,我把产品的筛选去掉,因为我不知道产成品的描述是啥,所以我用你的语句循环追溯下去,数据跑到了1600W行,虽然结果和我测算的一样,但是我不知道这样做 是不是有什么BUG在里面啊?

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注