Power BI M代码实现按句首大写拆分HTML提取的粘连句子

安全阿岳

2026-8-28

问题背景

正在对HTML提取的数据集做清洗，当前数据存在句子拆分错误：后一句的句首大写单词直接和前一句的末尾字符粘连，没有正确分隔。
预期拆分效果示例：
输入粘连文本：The boy plays with the ballThe Girl plays with the Console in a row
拆分后输出：
```
The boy plays with the ball
The Girl plays with the Console
```
现有基础M代码仅支持Power BI环境运行（调用了Excel版本不支持的Html.Table函数），代码如下：

let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://echa.europa.eu/registration-dossier/-/registered-dossier/14184/7/1"))}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([Column1], "General Population - Hazard via oral route") then [Column1] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> null)),
    #"Kept Last Rows" = Table.LastN(#"Filtered Rows", 1),
    #"Removed Other Columns" = Table.SelectColumns(#"Kept Last Rows",{"Custom"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Other Columns", {{"Custom", Splitter.SplitTextByDelimiter("</dd><dt>", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "Text", each Html.Table([Custom], {{"Custom",":root"}})),
    #"Expanded Text" = Table.ExpandTableColumn(#"Added Custom1", "Text", {"Custom"}, {"Custom.1"})
in
    #"Expanded Text"

需求：在上述现有M代码的基础上补充处理逻辑，实现按照大小写变化特征拆分粘连句子的效果。

实现方案

核心逻辑是通过正则匹配「非大写的句尾字符（小写字母、句末标点）后直接跟随大写句首字母」的粘连特征，在匹配位置插入临时分隔符后做列表拆分即可，修改后的完整代码如下：

let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://echa.europa.eu/registration-dossier/-/registered-dossier/14184/7/1"))}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([Column1], "General Population - Hazard via oral route") then [Column1] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> null)),
    #"Kept Last Rows" = Table.LastN(#"Filtered Rows", 1),
    #"Removed Other Columns" = Table.SelectColumns(#"Kept Last Rows",{"Custom"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Other Columns", {{"Custom", Splitter.SplitTextByDelimiter("</dd><dt>", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "Text", each Html.Table([Custom], {{"Custom",":root"}})),
    #"Expanded Text" = Table.ExpandTableColumn(#"Added Custom1", "Text", {"Custom"}, {"Custom.1"}),
    // 新增步骤：识别大小写粘连位置，插入临时分隔符后拆分句子
    #"Added Sentence Split" = Table.AddColumn(#"Expanded Text", "CleanedText", each 
        let
            // 正则匹配小写字母、句末标点后直接接大写字母的粘连位置，插入临时分隔标记
            processed = Text.ReplaceRegex([Custom.1], "([a-z\.\?\!])\s?([A-Z])", "$1#SPLIT_TAG#$2"),
            splitResult = Text.Split(processed, "#SPLIT_TAG#")
        in splitResult
    ),
    #"Expanded Split Result" = Table.ExpandListColumn(#"Added Sentence Split", "CleanedText"),
    #"Removed Blank Lines" = Table.SelectRows(#"Expanded Split Result", each not Text.IsWhitespace([CleanedText]))
in
    #"Removed Blank Lines"

注：Text.ReplaceRegex是Power BI 2022年之后版本内置的正则处理函数，若使用旧版本Power BI，可提前配置正则支持或自定义同名匹配函数即可。如果实际数据中存在数字、右括号等其他字符后直接粘连大写句首的场景，可自行调整正则第一个匹配组的规则，补充需要识别的前导字符即可。

内容的提问来源于stack exchange，提问作者Nick