合并日期参数表

下图为记录着客户存款的Data表格:

现在需要按照以下三个日期参数表，对以上表格的到期日所处于的时间范围进行归类。注：为了尽可能降低代码的维护成本，建议日期参数表的命名具备一定的特征，并且容易排序。因此，本案例的日期参数表的命名都符合"MB阿拉伯数字_用途英文简称"规律。

在讲解Table.SingleRow()的文章中，介绍了模糊匹配解决只有一个参数表的套路，只要重复进行该套路三次即可以解决本案例：

let
    Buffering_LST = Table.Buffer( MB1_LST ),
    AddCol_LST =
        Table.AddColumn(
            Data,
            "nRecord_LST",
            (x) ⇒
                Table.Last(
                    Table.RemoveLastN(
                        Buffering_LST,
                        (y) ⇒ y[起始日] ＞ x[到期日]
                    )
                ),
            type record
        ),
    ExpandrCol_LST =
        Table.ExpandRecordColumn(
            AddCol_LST,
            "nRecord_LST",
            {"日期范围"},
            {"日期范围_LST"}
        ),
    Buffering_LCR = Table.Buffer( MB2_LCR ),
    AddCol_LCR =
        Table.AddColumn(
            ExpandrCol_LST,
            "nRecord_LCR",
            (x) ⇒
                Table.Last(
                    Table.RemoveLastN(
                        Buffering_LCR,
                        (y) ⇒ y[起始日] ＞ x[到期日]
                    )
                ),
            type record
        ),
    ExpandrCol_LCR =
        Table.ExpandRecordColumn(
            AddCol_LCR,
            "nRecord_LCR",
            {"日期范围"},
            {"日期范围_LCR"}
        ),
    Buffering_NSFR = Table.Buffer( MB3_NSFR ),
    AddCol_NSFR =
        Table.AddColumn(
            ExpandrCol_LCR,
            "nRecord_NSFR",
            (x) ⇒
                Table.Last(
                    Table.RemoveLastN(
                        Buffering_NSFR,
                        (y) ⇒ y[起始日] ＞ x[到期日]
                    )
                ),
            type record
        ),
    ExpandrCol_NSFR =
        Table.ExpandRecordColumn(
            AddCol_NSFR,
            "nRecord_NSFR",
            {"日期范围"},
            {"日期范围_NSFR"}
        ),
    DataType =
        Table.TransformColumnTypes(
            ExpandrCol_NSFR,
            {
                { "日期范围_LST", type text },
                { "日期范围_LCR", type text },
                { "日期范围_NSFR", type text }
            }
        )
in
    DataType

由于现实中日期参数表可能远远多于3个并且Data表格的行数也可能不少，进行反复匹配和展开，可能会导致代码运行的时间过长。如果可以把多个日期参数表整合为一个表格，匹配和展开的次数将会减少为一次，这样就有可能达到优化的目的。为了使代码尽可能得简洁，需要准备好Semi_Finished表格, All_ColNames串列, All_Tables串列以及NoOfTable数字。以下为Semi_Finished表格所对应的代码：

let
    Source = #shared,
    ToTable = Record.ToTable(Source),
    Selection =
        Table.SelectRows(
            ToTable,
            each Text.StartsWith( [Name], "MB" )
        ),
    Sorting =
        Table.Sort(
            Selection,
            { "Name", Order.Ascending }
        )
in
    Sorting

#shared为M语言中的内置记录，该记录中的所有字段名称涵盖了所有由使用者生成的表格的名称，这些字段名称对应的字段值为具有该名称的表格。在前文中提及到本案例中所有参数表的名称符合"MB阿拉伯数字_用途英文简写"规律，因此可以通过筛选由内置记录转化而来的表格把所有的参数表找出来并排好序，这就是以上代码的意图。以下为Semi_Finished表格的图例：

得到Semi_Finished表格后，就可以把这个表格的Name列转化为All_ColNames串列:

let
    Outcome =
        List.Transform(
            Semi_Finished[Name],
            each
                Text.Combine(
                    {
                        "日期范围",
                        Text.AfterDelimiter( _, "_" )
                    },
                    "_"
                )
        )
in
    Outcome

在以上代码中，通过使用Text.AfterDelimiter()把所有参数表的名称转化为这些名称中代表用途的简称，然后把"日期范围"与这些简称结合在一起。All_Tables串列对应的代码非常简单，通过Table.Column()把Semi_Finished表格的Value列转化为串列：

let
    Outcome = Table.Column( Semi_Finished, "Value" )
in
    Outcome

NoOfTable数字代表参数表的数目，把Semi_Finished表格代入Table.RowCount()就可以得到该数字：

let
    Source = Table.RowCount( Semi_Finished )
in
    Source

作好以上准备后，就可以开始合并参数表了。合并的思路大致上可以归纳为：1）合并所有参数表， 2)提取合并表格的起始日列使之转化为串列并通过List.Distinct()移除重复的起始日，3)通过List.Sort()对移除重复后的串列按日期大小进行升序排列, 4)通过List.Transform()实现二重迭代，并按模糊匹配的思路把去重且升序后的每一个起始日所对应的三种时间范围找出来。以下为该思路所对应的代码：

let
    UnionAll = Table.Combine( All_Tables ), //注：对应步骤1)
    
    Distinction = List.Distinct( UnionAll[起始日] ), //注：对应步骤2)
    Sorting = List.Sort( Distinction, Order.Ascending ), //注：对应步骤3)
    //注：对应步骤4)
    Transformation =
        List.Transform(
            Sorting,
            (x) ⇒
                List.Combine(
                    {
                        { x },
                        List.Transform(
                            All_Tables,
                                (y) ⇒
                                    Record.Field(
                                        Table.Last(
                                            Table.RemoveLastN(
                                                y,
                                                (w) ⇒ w[起始日] ＞ x
                                            )
                                        ),
                                        "日期范围"
                                    )
                        )
                    }
                )
        ),
    // 注：TableType过程使用了动态生成type table[]的套路，详情请回顾讲解Table.Schema()的文章
    TableType =
        Expression.Evaluate(
            Text.Combine(
                {
                    "type table [ 起始日 = date, ",
                    Text.Combine(
                        List.Transform(
                            All_ColNames,
                            each
                                Text.Combine(
                                    {
                                        _,
                                        " = text"
                                    }
                                )
                        ),
                        ","
                    ),
                    " ]"
                }
            )
       ),
    ToTableByRow = Table.FromRows( Transformation, TableType )
in
    ToTableByRow

以上查询产生如下MaturityBand_Cons3表格:

在得到MaturityBand_Cons3表格之后，就通过把该表格与Data表格套用在前文提及的模糊匹配套路上，在这里就不再累述了，详情请回顾介绍Table.SingleRow()的文章或者本案例中Solution1表格所对应的代码。虽然采用合并日期参数表的思路需要使用不常见的二重迭代或者List.TransformMany()与Table.Group()的组合，但是由于一般来说日期参数表的行数很少（没有见过上百行的日期参数），所以合并所带来的成本应该远远少于一次匹配与展开的成本，从而达到减少运行时间的效果。如果读者对左连接有足够的理解，还可以使用另一种思路进行合并参数表，这种思路大致归纳为: 1)合并所有参数表，2)使用Table.Distinct()移除合并后的起始日列的重复日期，3）对去重后的起始日列进行升序排序，4）移除无用的日期范围列，5）对步骤四所得的表格使用三次左连接，分别连接MB1_LST表格, MB2_LCR表格和MB3_NSFR表格，6) 向下填充展开的日期范围_LST列，日期范围_LCR列和日期范围_NSFR列. 以下为这种思路对应的代码：

let
    UnionAll = Table.Combine( All_Tables ), //注：对应步骤1）
    Distinction = Table.Distinct( UnionAll, "起始日" ), //注：对应步骤2）
    Sorting = Table.Sort( Distinction, "起始日" ), //注：对应步骤3）
    RemoveCol = Table.RemoveColumns(Sorting, "日期范围"), //注：对应步骤4）
    //注：对应步骤5）
    LeftJoins =
        List.Accumulate(
            List.Zip(
                {
                    All_Tables,
                    All_ColNames
                }
            ),
            RemoveCol,
            (x,y) ⇒
                Table.ExpandTableColumn(
                    Table.AddJoinColumn(
                        x,
                        "起始日",
                        y{0},
                        "起始日",
                        "nTable"
                    ),
                    "nTable",
                    {"日期范围"},
                     { y{1} }
                )
        ),
    FillDown = Table.FillDown( LeftJoins, All_ColNames ) //注：对应步骤6）
in
    FillDown

虽然没有经过严谨的测试，个人认为使用左连接的思路更为高效。