各位好!
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
@斜杠星号
中间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
源治本的办法，还是从数据源规范做起。
手动排序和循环排序是一样的效果，多写一步作为参考。
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
结果
我的意思是: [对BOM表列中的分支层级, 只要横杠-号后面不是字母而仅有1位数字的时候, 全部在前面加0补充为2位数字, 这样就解决了后面要正常排序的需求.]
源数据为: DE3452FEE.DT 23675G-2-F-8-DG-10-K-6
期望结果: DE3452FEE.DT 23675G-02-F-08-DG-10-K-06
观察了下，直接用的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
源
@斜杠星号
中间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
源治本的办法，还是从数据源规范做起。
@焦棚子
调试后还发现一个问题, 就是子级的编码要么是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)) )
这个应该可以用Table.Sort一步出排序结果，只是Table.Sort 的第二参数要花点心思。
@飞天篮球
1、如果把表里的第二个“4G0 199 379AQ” 改成“14G0 199 379AQ” ，按目前的数据源，它应该排在哪里？最后？
2、你想要的排序规则是不是这样的，以“4G0 199 379AQ”为例，分别按 4、G、0、199、379、AQ，这样的顺序排序？
======
（以下语句和方法，得到了畅老板和晨星大佬的指点，谢谢）
有点复杂，也有考虑不到边的情况。我的想法就是把每个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
排序@斜杠星号
昏倒，跟着畅老板忙活一上午，你居然没有那个函数，哈哈哈哈哈。。。。
还有一个写法，跟着畅老板学的。 一个是把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
排序有朋友没有更新到最新版本的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
排序关于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}
结果如下图：
如果要传递双参数，比如字符串对象和补全长度都作为参数，那就要这样写：
= 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第二参数的写法构造引申出这些内容，个人觉得信息量不少。
以上供参考，不妥之处请指正。
