一个不规则的提取引发的 PowerQuery 中的 M 语言的练习

问题：

提取网址中的页面ID

方法1 正则表达式1(效率一般)

Web.Page("<script> document.write('"&[网址]&"'.match(/(pw|mw|my|py)-(\w+-)+\w+(?=\/)/g))</script>")
[Data]{0}[Children]{0}[Children]{1}[Text]
{0}

方法2 正则表达式2(效率一般)

Web.Page("<script> document.write('"&[网址]&"'.match(/([pm][wy])-[^/]*/g))</script>")
[Data]{0}[Children]{0}[Children]{1}[Text]
{0}

方法3

List.RemoveNulls(List.Transform({"pw-","mw-","my","py-"},(x)=>[a=Text.BetweenDelimiters([网址],x,"/"),b=if a<>"" then x&a else null][b])){0}?

方法4 目前测试效率最高

List.RemoveNulls(List.TransformMany(
Text.Split([网址],"/"),
each {"py","pw","my","mw"},
(x,y) => if Text.StartsWith(x,y) then x else null 
)){0}?

List.Max(List.TransformMany(
Text.Split([网址],"/"),
each {"py","pw","my","mw"},
(x,y) => if Text.StartsWith(x,y) then x else null 
))

方法5

List.Mode(
Text.Split([网址],"/")&{"py","pw","my","mw"," "},(x)=>Text.Start(x,2)

)

方法6 效率与方法4相当

List.Accumulate(Text.Split([网址],"/"),"",(x,y)=>x&(if List.Contains({"py","pw","my","mw"},y,(a,b)=>Text.StartsWith(b,a)) then y else ""))

雷公子 | 简快EXCEL【powerbipro】

• http://powerbi.cc