匹配范围

在领导的要求下，现需要对IT部门生成的存款表进行集中化加工，方便其他同事共享。其中一项加工要求根据已经合并好的日期表参数表把存款表中的到期日所处于的日期范围匹配出来。考虑到存款表的行数比较多并且还需要添加其他的计算列，所以需要比较不同的匹配方法。

因为某个到期日处于某个时间范围时必须满足该日期大于等于这个日期范围的起始日并且小于等于这个日期范围的结束日，所以可以利用这一逻辑关系使用Table.AddColumn()+Table.SelectRows()的组合把参数表中满足条件的唯一行匹配出来，最后使用Table.ExpandTableColumn()展开嵌套的表格，以下为该思路对应的代码：

let
    AddCol =
        Table.AddColumn(
            Data,
            "nTable",
            (x)=˃
                Table.SelectRows(
                    ParaTB,
                    (y)=˃ y[起始日] ˂= x[到期日] and y[结束日] ˃= x[到期日]
                ),
            type table
        ),
    Expansion =
        Table.ExpandTableColumn(
            AddCol,
            "nTable",
            {
                "日期范围_LCR",
                "日期范围_LST",
                "日期范围_NSFR"
            }
        ),
    DataType =
        Table.TransformColumnTypes(
            Expansion,
            {
                {"日期范围_LCR", type text},
                {"日期范围_LST", type text},
                {"日期范围_NSFR", type text}
            }
        )
in
    DataType

使用以上代码，几乎没有延迟就可以在使用者界面中获得预览结果。但是如果需要把结果输出到工作簿或者PowerPivot，则需要比较多的响应时间。笔者测试了在不同行数的情况下输出到工作簿所需要的时间，其结果如下图所示（单位为秒）。

第二种思路借鉴了工作表函数组合Index()+Match()，首先从参数表的最后一行出发把所有参数表中起始日大于到期日的行移除，然后取剩余行中的最后一行，最后展开目标列就得到所需的计算列，以下为该思路对应的代码：

let
    AddCol =
        Table.AddColumn(
            Data,
            "nRecord",
            (x)=˃
                Table.Last(
                    Table.RemoveLastN(
                        ParaTB,
                        (y)=˃ y[起始日] ˃ x[到期日]
                    )
                ),
            type record
        ),
    Expansion =
        Table.ExpandRecordColumn(
            AddCol,
            "nRecord",
            {
                "日期范围_LCR",
                "日期范围_LST",
                "日期范围_NSFR"
            }
        ),
    DataType =
        Table.TransformColumnTypes(
            Expansion,
            {
                {"日期范围_LCR", type text},
                {"日期范围_LST", type text},
                {"日期范围_NSFR", type text}
            }
        )
in
    DataType

使用以上代码，也可以几乎没有延迟就可以在使用者界面中获得预览结果。但是如果需要把结果输出到工作簿或者PowerPivot，则需要比第一种思路更多的响应时间。笔者测试了在不同行数的情况下输出到工作簿所需要的时间，其结果如下图所示（单位为秒）。

第三种思路从参数表出发，通过简单的步骤把参数表转化为能够与存款表形成一对多关系的结构。首先需要取出存款表中所有的到期日使之化为串列，然后使用List.Distinct()移除其中的重复项。接着，再次利用第一种思路中提及的逻辑关系，对参数表使用Table.AddColumn()+List.Select()的组合，把所有满足关系的到期日筛选出来使之成为串列并内嵌于参数表的当前行中。最后，使用Table.ExpandListColumn()展开目标列。以下为思路对应的代码：

let
    Listing =
        Table.Column(
            Data,
            "到期日"
        ),
    Distinction = List.Distinct( Listing ),
    Buffering = List.Buffer( Distinction ),
    AddCol =
        Table.AddColumn(
            ParaTB,
            "去重的日期",
            (x)=˃
                List.Select(
                    Buffering,
                    (y)=˃ x[起始日] ˂= y and x[结束日] ˃= y
                ),
            type list
        ),
    Expansion =
        Table.ExpandListColumn(
            AddCol,
            "去重的日期"
        ),
    
    RemoveRows =
        Table.SelectRows(
            Expansion,
            each [去重的日期] ˂˃ null
        ),
    DataType =
        Table.TransformColumnTypes(
            RemoveRows,
            {
                "去重的日期",
                type date
            }
        )
in
    DataType

把参数表转化为可以与存款表形成一对多关系的结构后，还需要使用内连接并展开目标列才完成存款表的加工，以下为剩余的步骤：

let
    Source =
        Table.NestedJoin(
            Data,
            "到期日",
            IntermediateTB,
            "去重的日期",
            "中间表",
            JoinKind.Inner
        ),
    Expansion =
        Table.ExpandTableColumn(
            Source,
            "中间表",
            {
                "日期范围_LST",
                "日期范围_LCR",
                "日期范围_NSFR"
            }
        )
in
    Expansion

使用第三种思路，需要非常之长的更新时间才能在使用者界面中获得预览结果，但是输出到工作簿或者PowerPivot中所使用的时间较前两种方法少很多。笔者测试了在不同行数的情况下输出到工作簿所需要的时间，其结果如下图所示（单位为秒）。

最后一种思路也是对参数表进行改造，但只适用于整数。首先需要求出最小的起始日，然后求出最大的到期日。得到这两个日期后，使用List.Dates()生成两者之间（包括左右端点）的日期并调用Table.FromValue()使之转化为表格。之后，以这个表格为左表，参数表为右表，进行左连接。完成连接后，展开目标列，并对这些列进行向下填充。以下为最后一种思路对应的代码：

let
    MaxDate = List.Max( Data[到期日] ),
    MinDate = List.Min( ParaTB[起始日] ),
    DateSeries =
        List.Dates(
            MinDate,
            Duration.TotalDays( MaxDate - MinDate ) + 1,
            Duration.From( 1 )
        ),
    ToTable =
        Table.FromValue(
            DateSeries,
            [DefaultColumnName="去重的日期"]
        ),
    LeftJoin =
        Table.AddJoinColumn(
            ToTable,
            "去重的日期",
            ParaTB,
            "起始日",
            "nTable"
        ),
    Expansion =
        Table.ExpandTableColumn(
            LeftJoin,
            "nTable",
            {
                "日期范围_LST",
                "日期范围_LCR",
                "日期范围_NSFR"
            }
        ),
    Sorting =
        Table.Sort(
            Expansion,
            {
                "去重的日期",
                Order.Ascending
            }
        ),
    
    FillDown =
        Table.FillDown(
            Sorting,
            {
                "日期范围_LST",
                "日期范围_LCR",
                "日期范围_NSFR"
            }
        ),
    DataType =
        Table.TransformColumnTypes(
            FillDown,
            {
                "去重的日期",
                type date
            }
        )
in
    DataType

类似于第三种思路，仍需要进行一次内连接才能完成加工。使用第四种思路，需要非常之长的更新时间才能在使用者界面中获得预览结果，但是输出到工作簿或者PowerPivot中所使用的时间较第一种和第二种方法少很多。笔者测试了在不同行数的情况下输出到工作簿所需要的时间，其结果如下图所示（单位为秒）。