对多级 BOM 文本列进行正确排序

Power Query 斜杠星号 ⋅ 于 2019-01-16 11:01:09 ⋅ 最后回复由 斜杠星号 2019-01-21 12:23:41 ⋅ 3800 阅读

各位好!
1-现在生成BOM清单时遇到1个文本内容排序的问题, 就是因为BOM列里包括有字母符号数字在一起的内容, 导致PowerQeury自带的排序功能升序并不能按预期的需求进行排序, 对比说明如下.
2- 每级BOM号间用"-"分开, 子级编号要么是A~Z, 要么是0~99, BOM层级最多可能达到10级, 正是因为有数字导致子级编号数位不统一, 而Excel本身的排序功能是从左对右对字符进行排序的, 这样如果有3个产品 A-1 A-2 A-10的话, Excel排序就成了A-1 A-10 A-2, 明显不符合需求.
3- 个人也尝试用Excel公式的补位思路在PowerQuery里实现对齐后排序, 但代码步骤判断写得太复杂了, 所以请各位大神看看用UDF来实现正确排序, 谢谢!
ps: 新手操作, 无法上传图片, 请看网盘链接-->DEMO-->Q&A问答-->附件PQ多级BOM文本排序求助.xlsx, 一目了然.
Link: https://pan.pbihub.cn/index.php/f/1004

本帖已被设为精华帖!
本帖由 Alex 于 5年前 加精
最佳答案
  • 焦棚子 微软 MVP
    2019-01-17 13:09:40

    @斜杠星号
    中间Text.Replace(Text.Replace(x,"-"&y,"-0"&y) ,"0"&y&"0",y&"0")
    第二次替换的问题,因为你提供的数据源中没有你这样的纯数字的操作,所以使用了这个办法,没有去考虑容错的问题。
    治标的办法是:

    let
        源 = 
    Table.Sort( 
        Table.Buffer(
            Table.TransformColumns(
                Excel.CurrentWorkbook(){[Name="data"]}[Content]
                    ,{"ID",(L)=>List.Accumulate({"1".."9"},L,(x,y)=> Text.Replace(Text.Replace(x,"-"&y,"-0"&y) ,"-0"&y&"0","-"&y&"0"))}
            )
    
        )
    ,{"ID",0}
    )
    in
        源

    治本的办法,还是从数据源规范做起。

回复数量: 18
  • 焦棚子 微软 MVP
    2019-01-16 15:20:00

    手动排序和循环排序是一样的效果,多写一步作为参考。

    let
        源 = Excel.CurrentWorkbook(){[Name="data"]}[Content],
        副本列 = Table.AddColumn(源, "副本", each [ID]),
        拆 = Table.SplitColumn(副本列,"副本",each Text.SplitAny(_," .-"),{"A".."G"}),
        类型 = Table.Buffer(Table.TransformColumnTypes(拆,{{"D", Int64.Type}, {"B", Int64.Type}, {"F", Int64.Type}})),
        手动排序 = Table.Sort(类型, {{"A", 0}, {"B", 0}, {"C", 0}, {"D", 0}, {"E", 0}, {"F", 0}, {"G", 0}} ),
        循环排序 = Table.Sort(类型, List.Accumulate({"A".."G"},{},(x,y)=>x&{{y,0}}) ),
        结果 = Table.SelectColumns(循环排序,{"ID"})
    in
        结果

    file

  • 斜杠星号
    2019-01-16 17:33:03

    @焦棚子 为了世界和平 谢谢焦老师的指点!
    不知道可否用 List.Accumulate + List.ReplaceValue 实现替换? [您的Split+Buffer考虑得很周全, 我想多几种解题思路]
    替换时把最后1个 -x 单独提取出来处理, [如果有]前面的 -x- 就可以用循环来替换了, 不知道我的这个想法可行吗?

  • 斜杠星号
    2019-01-16 17:48:46

    我的意思是: [对BOM表列中的分支层级, 只要横杠-号后面不是字母而仅有1位数字的时候, 全部在前面加0补充为2位数字, 这样就解决了后面要正常排序的需求.]
    源数据为: DE3452FEE.DT 23675G-2-F-8-DG-10-K-6
    期望结果: DE3452FEE.DT 23675G-02-F-08-DG-10-K-06

  • 焦棚子 微软 MVP
    2019-01-16 22:16:34

    @斜杠星号

    观察了下,直接用的Text.Replace,第一种方法会更普遍使用些,下面的方法针对你后面提出的需求合适些。
    供参考吧。

    let
        源 = 
    Table.Sort( 
        Table.Buffer(
            Table.TransformColumns(
                Excel.CurrentWorkbook(){[Name="data"]}[Content]
                    ,{"ID",(L)=>List.Accumulate({"1".."9"},L,(x,y)=> Text.Replace(Text.Replace(x,"-"&y,"-0"&y) ,"0"&y&"0",y&"0"))}
            )
    
        )
    ,{"ID",0}
    )
    in
        源

    file

  • 斜杠星号
    2019-01-17 11:35:32

    @焦棚子
    谢谢大神!
    List.Accumulate + Text.Replace的组合用法甚为精妙!
    但是目前遇到一个问题, 就是当BOM列内容里包含多个0但没有横杠 "-"号的时候, 会出现误杀现象:
    源数据为: 94837505001
    期望结果: 94837505001
    当前结果: 9483755001 ===> [ 这里没有 "-" ,但代码却把中间内容'505'之间的0给杀掉了, 何解? ]

  • 焦棚子 微软 MVP
    2019-01-17 13:09:40

    @斜杠星号
    中间Text.Replace(Text.Replace(x,"-"&y,"-0"&y) ,"0"&y&"0",y&"0")
    第二次替换的问题,因为你提供的数据源中没有你这样的纯数字的操作,所以使用了这个办法,没有去考虑容错的问题。
    治标的办法是:

    let
        源 = 
    Table.Sort( 
        Table.Buffer(
            Table.TransformColumns(
                Excel.CurrentWorkbook(){[Name="data"]}[Content]
                    ,{"ID",(L)=>List.Accumulate({"1".."9"},L,(x,y)=> Text.Replace(Text.Replace(x,"-"&y,"-0"&y) ,"-0"&y&"0","-"&y&"0"))}
            )
    
        )
    ,{"ID",0}
    )
    in
        源

    治本的办法,还是从数据源规范做起。

  • 斜杠星号
    2019-01-17 14:42:54

    @焦棚子
    调试后还发现一个问题, 就是子级的编码要么是1~99, 要么是A~Z, 所以如果在-和-之间的数字是11或99之间的话, 那么上面的代码还是就会多出一位0来,
    源数据为: 8R0 199 381EL-11-H
    期望结果: 8R0 199 381EL-11-H
    当前结果: 8R0 199 381EL-011-H ===>. [因为 "-0"&y&"0" 只能处理 '-01'0 ~ '09'0, 所以这里的011就被遗漏了.]
    如焦老师所言, 第一步规范数据源才是正道, 感谢焦老师的不厌其烦, 我于是就用了下面这个大笨瓜式的3层Text.Replace处理法, 希望焦老师不要打我, 假如焦老师能再指教优化一下, 万分感谢焦老师!

    (Len)=>List.Accumulate({"1".."9"},Len,(x,y)=>(if not Text.Contains(x,"-")then x else Text.Replace(Text.Replace(Text.Replace(x,"-"&y,"-0"&y),"-0"&y&"0","-"&y&"0"),"-0"&y&y,"-"&y&y)) )

  • 焦棚子 微软 MVP
    2019-01-17 14:47:28

    @斜杠星号
    能解决问题就是对的。
    另外不要一下就提一个需求一下就提一个需求,这样会失去完整的思考过程。
    上附件,把需求说清楚,或许根本不会用这方法哈。

  • 斜杠星号
    2019-01-17 15:04:55

    @焦棚子
    焦老师教训得是, 我主要是想头脑风暴一下, 多学习各类代码思路, 再次感谢!

  • 飞天篮球 Talk is cheap, show me your code.
    2019-01-18 20:46:05

    @斜杠星号
    有个问题,

    1、如果把表里的第二个“4G0 199 379AQ” 改成“14G0 199 379AQ” ,按目前的数据源,它应该排在哪里?最后?

    2、你想要的排序规则是不是这样的,以“4G0 199 379AQ”为例,分别按 4、G、0、199、379、AQ,这样的顺序排序?

  • 飞天篮球 Talk is cheap, show me your code.
    2019-01-18 20:54:06

    这个应该可以用Table.Sort一步出排序结果,只是Table.Sort 的第二参数要花点心思。

  • 斜杠星号
    2019-01-19 11:45:52

    @飞天篮球
    1、如果把表里的第二个“4G0 199 379AQ” 改成“14G0 199 379AQ” ,按目前的数据源,它应该排在哪里?最后?

    在BOM主名称内如果出现这样的情况, 因为名称内有空格或点分隔, 14G0相对于4G0排在最后.

    2、你想要的排序规则是不是这样的,以“4G0 199 379AQ”为例,分别按 4、G、0、199、379、AQ,这样的顺序排序?

    对头, 就是这样的排序模式.

    ======

    • 目前BOM列分为2部分, 主名称和分级符号-后面的子名称.
    • 主名称内包含字母, 数字, 点号, 空格, 但基于编码规则, 主名称内不会有横杠.[因为横杠作为分级符号]
    • 子名称可能有多级, 每级之间用横杠-符号分隔, 子名称要么是A~Z的字母,要么是1~99的数字, 就是说子名称排序时要考虑到数位补齐的问题, 不然会出现升序排列后10在2前面的现象.
  • 飞天篮球 Talk is cheap, show me your code.
    2019-01-19 12:21:51

    @斜杠星号

    (以下语句和方法,得到了畅老板和晨星大佬的指点,谢谢)

    有点复杂,也有考虑不到边的情况。我的想法就是把每个ID拆开,只要是数字连在一起的就一起不拆开,有分隔符的就拆开,字母连在一起的还是连在一起,比如"8WD.199.371B-10-A-2"拆成8,WD,199,371,B,10,A,2这样子后,再按每个元素的先后位次按照其对应字符的序列进行排序。不知道这个逻辑是不是对的? 大概弄了一下,按照目前数据源的情况测试,结果是对的。有两种方法,一个是用Splitter.SplitTextByCharacterTransition,一个是用正则,正则简单但速度慢。以下仅供参考,普适性有待检验。源就是你表格左侧的那一列。

    方法一:Splitter拆

    let
        源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    
        排序 = Table.Sort( 源, List.Transform( {0..15}, (x)=>each 
                                     let fx = (x)=>Splitter.SplitTextByCharacterTransition(x,each not List.Contains(x,_)),
                                          l = List.Combine(List.TransformMany( Text.SplitAny([#"Excel排序功能-升序结果"],".- "), 
                                                                               each fx({"0".."9"})(_), 
                                                                               (x,y)=>fx({"A".."Z"})(y))) 
                                      in try Number.From(l{x}) otherwise l{x}?))
    in
        排序

    方法二:正则拆

    let
        源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    
        排序 = Table.Sort(源, List.Transform({0..15},(x)=>each 
                                     Table.TransformColumns( Web.Page("<script>
                                                                               document.write('"&[#"Excel排序功能-升序结果"]&"'.match(/\d+|[A-Z]+/g).join('<br>'))
                                                                       </script>")[Data]{0}[Children]{0}[Children]{1},
                                                             {"Text",(x)=>try Number.From(x) otherwise x})  [Text] {x}?))
    in
        排序

    还有一个就是你说的补全位数后排序:

    let
        源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    
        排序 = Table.Sort( 源, List.Transform( {0..15}, (x)=>each 
                                 let fx = (x)=>Splitter.SplitTextByCharacterTransition(x,each not List.Contains(x,_)),
                                      l = List.Transform(List.Combine(List.TransformMany( Text.SplitAny([#"Excel排序功能-升序结果"],".- "), 
                                                                               each fx({"0".."9"})(_), 
                                                                               (x,y)=>fx({"A".."Z"})(y))),(t)=>Text.PadStart(t,2,"0"))
                                      in l{x}?))
    in
        排序
  • 斜杠星号
    2019-01-19 13:05:36

    @飞天篮球
    1- 谢谢大神, 我这边用的是2018-10月更新的Excel版本, 没有找到Splitter.SplitTextByCharacterTransition这个函数, 你这个是最新的版本才有的函数吧?
    2- 阁下的UDF组合函数用法炉火纯青, 请容我慢慢消化再理解, 谢谢!

  • 飞天篮球 Talk is cheap, show me your code.
    2019-01-19 13:30:01

    @斜杠星号
    昏倒,跟着畅老板忙活一上午,你居然没有那个函数,哈哈哈哈哈。。。。
    还有一个写法,跟着畅老板学的。 一个是把fx单独写了一步,另一个是把排序的内容先拆了,用0补全为5位数后,合并再排序。供参考:

    let
        源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    
        fx = let fx = (x)=>Splitter.SplitTextByCharacterTransition(x,each not List.Contains(x,_))  in fx,
    
        排序 = Table.Sort( 源, each Text.Combine(
                                                 List.Transform( 
                                                                 List.Combine(
                                                                              List.TransformMany( Text.SplitAny([#"Excel排序功能-升序结果"],".- "),
                                                                                                   each fx({"0".."9"})(_), 
                                                                                                   (x,y)=>fx({"A".."Z"})(y))),
                                                                (t)=>Text.PadStart(t,5,"0") ) ) )
    in
        排序
  • 飞天篮球 Talk is cheap, show me your code.
    2019-01-19 22:21:51

    有朋友没有更新到最新版本的PQ,没有Splitter.SplitTextByCharacterTransition,这个函数有点像正则里的零宽,断的只是一个位置。再提供两种补位的方法:

    一个是用JS自定义补位,并配合replace把字符串中的数字替换成补位后的文本数值,优点是简洁,缺点是效率欠佳:
    单独的M+JS数字补位方法,连续数字用“0”补全为10位,这个补位的取值必须要大于最大连续数字字符串的长度:

    = Web.Page("<script>function Pad(num, len) {return (Array(len).join('0') + num).slice(-len);}
                        document.write('8WD.199.371B-10-A-2'.replace(/\d+/g,function(x){return Pad(x,10)}))
                      </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}

    运用到排序里:

    let
        源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    
        排序 = Table.Sort(源, each Web.Page("<script> function Pad(num, len) {return (Array(len).join('0') + num).slice(-len)}
                                                     document.write('"&[#"Excel排序功能-升序结果"]&"'.replace(/\d+/g,function(x){return Pad(x,20)}))
                                           </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0})
    in
        排序

    另一种是用List.Accumulate对字符串中的数字进行补位,本来以为用acc会简单一点,写完奔溃了,有点复杂,就当交流参考吧。希望大神能优化一下acc的写法。

    单独的accumulate补位语句,连续数字用“0”补全为5位:

    = let l=Text.ToList("8WD.199.371B-10-A-1") 
              in List.Accumulate( l&({{"1"},{""}}{Byte.From(List.Contains({"0".."9"},List.Last(l)))}),
                                  {"","",l{0}},
                                  (s,c)=>if List.ContainsAll({"0".."9"},{s{2},c}) or (not List.ContainsAny({"0".."9"},{s{2},c}))
                                         then {s{0},s{1}&c,c} 
                                         else {s{0}&{s{1},Text.PadStart(s{1},5,"0")}{Byte.From(s{1}>="0" and s{1}<="9")},c,c}){0}
    

    运用到Table.Sort里:

    let
        源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    
        排序 = Table.Sort( 源, each let l=Text.ToList([#"Excel排序功能-升序结果"]) 
                                    in List.Accumulate( l&({{"1"},{""}}{Byte.From(List.Contains({"0".."9"},List.Last(l)))}),
                                                        {"","",l{0}},
                                                        (s,c)=>if   List.ContainsAll({"0".."9"},{s{2},c}) 
                                                                    or 
                                                                    (not List.ContainsAny({"0".."9"},{s{2},c})) 
                                                               then {s{0},s{1}&c,c} 
                                                               else { s{0} 
                                                                      & { s{1},Text.PadStart(s{1},5,"0") }{Byte.From(s{1}>="0" and s{1}<="9")},c,c}){0})
    in
        排序
  • 飞天篮球 Talk is cheap, show me your code.
    2019-01-20 16:48:04

    关于M+JS用“0”补齐位数,经畅心老板斧正,精炼了一下写法。一是JS中的自定义补齐函数用了递归,这样的好处是避免了上述补充方法中需要人为目测一个最长的连续数字长度的做法,可以做到不够位数则补全,足位数的保持原来的状态;第二个是效仿了M的隐式传递,精简了语句。比如,以下例子中把连续数字不足5位的用0补全,其中的function Pad...return Pad就是JS中的递归, replace正则替换的第二参数可以是function,且做了隐式传递,这类似于M中的List.Transform({"1".."3"},Number.From)省略了each _这样的写法。

    以下写法是把补全长度在UDF中写成了一个常量:

    = Web.Page("<script>function Pad(s) {if(s.length>=5) {return s}return Pad('0'+s)}
                        document.write('8WD.123456.371B-10-A-2'.replace(/\d+/g,Pad))
               </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}
    

    结果如下图:

    file

    如果要传递双参数,比如字符串对象和补全长度都作为参数,那就要这样写:

    = Web.Page("<script>function Pad(s,len) {if(s.length>=len) {return s} return Pad('0'+s,len)}
                        document.write('8WD.123456.371B-10-A-2'.replace(/\d+/g,function(x){return Pad(x,5)}))
                </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}

    从Table.Sort第二参数的写法构造引申出这些内容,个人觉得信息量不少。

    以上供参考,不妥之处请指正。

  • 斜杠星号
    2019-01-21 12:23:41

    @飞天篮球
    我只想说:100: " 地球人已经阻止不了你们啦 ! "

暂无评论~~
  • 请务必阅读并严格遵守《社区管理规范与使用说明》
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
  • 支持表情,使用方法请见 发送表情,可用的 Emoji 见 :metal: :point_right: Emoji 列表 :star: :sparkles:
  • 上传图片, 支持拖拽和剪切板粘贴上传, 格式限制 - jpg, png, gif
  • 不支持上传附件,请尽可能用文字和图片将问题描述清楚,如实在需要上传附件,可上传到 共享网盘 后分享链接
  • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
  请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!
Ctrl+Enter