正则表达式(优化篇)

现有如下所示文档,其中的每一列(总共9列)算上前置的空白字符共有14个字符,现要求使用Power Query读取该文档并且把数据准确地分为9列。

这个问题的最优解是使用Csv.Document()的等宽分列功能,代码如下:

let
    Source =
        Csv.Document(
            File.Contents("D:\OneDrive\PQ Fans\Basketball\Data.csv"),
            null,
            {1, 15, 29, 43, 57, 71, 85, 99, 113, 127}
        ),

    RemoveCol =
        Table.RemoveColumns(
            Source,
            {"Column10"}
        )
in
    RemoveCol

在没有发现以上方法之前,笔者曾向篮球大哥请教了如何使用正则表达式解答以上问题,今天将与大家分享篮球大哥的巧妙解答。在这之前,让我们先看看使用正则表达式解答的常规方法,代码如下:

let
    Source =
        Csv.Document(
            File.Contents(
                "D:\OneDrive\PQ Fans\Basketball\Data.csv"
            ),
            {"1"},
            "#(lf)"
        ),

    Add_Col =
        Table.AddColumn(
            Source,
            "Reg_Test",
            each
                Web.Page(
                    Text.Combine(
                        {
                            "˂script˃ ",
                            "var s = '",
                            [1],
                            "'; ",
                            "var p = /\d*,?\d*\.?\d+|([a-zA-Z]+\s*){1,3}|\s{14}/g; " ,
                            "var r = s.match(p).join('|');",
                            "document.write(r); ",
                            "˂script˃"
                        }
                    )
                )[Data]{0}[Children]{0}[Children]{1}[Text]{0},
            type text
        ),

    RemoveCol =
        Table.RemoveColumns(
            Add_Col,
            {"1"}
        ),

    SplitCol =
        Table.SplitColumn(
            RemoveCol,
            "Reg_Test",
            Splitter.SplitTextByDelimiter("|"),
            List.Transform(
                {"1".."9"},
                each
                    Text.Combine(
                        {
                            "Column",
                            _
                        }
                    )
            )
        )
in
    SplitCol

以上方法为源数据添加了一计算列,这个计算列中的每一行都调用了一次Web.Page(),每一次调用都会使用\d*,?\d*\.?\d+|([a-zA-Z]+\s*){1,3}|\s{14}这个正则表达式把当前文本的每一列(总共九列)的值匹配出来。匹配出来的9个值实际上是以数组形式存在的,所以可以使用Join()方法以|为分隔符把这些值连接起来。由于这个方法每一行都调用了一次Web.Page(),所以运行的时间会较长,如果数据拓展到10万行,运行时间接近50秒(注:\d*,?\d*\.?\d+用于匹配数字,([a-zA-Z]+\s*){1,3}匹配至少出现一次但不多于3次的英文单词或者字母,\s{14}匹配刚好出现14次的空白字符)。

篮球大哥的方法引入了数组,这样可以使Web.Page()的调用次数减少至一次,代码如下:

let
    Source =
        Csv.Document(
            File.Contents(
                "D:\OneDrive\PQ Fans\Basketball\Data.csv"
            ),
            {"1"},
            "#(lf)"
        ),

    Json =
        Web.Page(
            Text.Combine(
                {
                    "˂script˃ arr = ",
                    Text.FromBinary(
                        Json.FromValue(
                            Table.Column(
                                Source,
                                "1"
                            )
                        )
                    ),
                    "; ",
                    "ptn = /\d*,?\d*\.?\d+|([a-zA-Z]+\s*){1,3}|\s{14}/g; ",
                    "rsl = []; ",
                    "for (i in arr){rsl.push('""' + arr[i].match(ptn).join('|') + '""')}; ",
                    "document.write('{' + rsl + '}');",
                    "˂script˃"
                }
            )
        )[Data]{0}[Children]{0}[Children]{1}[Text]{0},

    Result =
        Table.FromList(
            Expression.Evaluate(Json),
            Splitter.SplitTextByDelimiter("|"),
            List.Transform(
                {"1".."9"},
                each
                    Text.Combine(
                        {
                            "Column",
                            _
                        }
                    )
            )
        )

in
    Result

以上方法最关键的一步在于使用Text.FromBinary()+Json.FromValue()的组合把源数据转换为以文本形式存在的数组。该数组会用于循环的过程,在每一次循环中都会用match()方法把九个值匹配出来。因为这九个值是以数组形式存在的,所以可以使用Join()方法以|为分隔符把这些值连接起来,之后还需要在最左边与最右边各加两个双引号。最后,连接的结果会通过push方法加入至空数组(即[])中,使用document.write()输出时需要在输出结果左右各加一个大括号,这样Expression.Evaluate()才会把输出的结果识别为Power Query的List。由于以上方法只使用了一次Web.Page(),所以运行时间大幅度减少,如果数据拓展至10万行,运行时间大约为3秒。

附件

5 Replies to “正则表达式(优化篇)”

  1. 最优解还可以更简洁,一步到位,不需要删除列这一步,代码如下:
    let
    Source =Csv.Document(File.Contents("D:\OneDrive\PQ Fans\Basketball\Data.csv"),9,List.Numbers(1, 10, 14),ExtraValues.Ignore)
    in
    Source

  2. 还可以在导入时不拆分,而是直接导入为一列,然后用拆分列功能进行拆分,配合拆分器使用,更具灵活性,能够适应各种不同文档格式。代码如下:
    let
    Source=Csv.Document(File.Contents("D:\OneDrive\PQ Fans\Basketball\Data.csv")),
    用位置分列 = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByRepeatedLengths(14))
    in
    用位置分列

发表回复

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