合并日期参数表(二)

笔者在工作中发现了无须循环就能实现合并日期参数表的方法,本文将与大家分享该思路。以下为需要合并的3个日期参数表:

表格: MaturityBand_LST
表格: MaturityBand_LCR
表格:MaturityBand_NSFR

合并以上日期参数表需要如下的代码:

let
    Path = "D:\OneDrive\PQ Fans\Consolidated Maturity Table\Parameter Table.xlsx",

    wkBook =
        Excel.Workbook(
            File.Contents(Path),
            true
        ),

    Filtering =
        Table.SelectRows(
            wkBook,
            each
                Text.StartsWith([Item], "MaturityBand")
            and
                [Kind] = "Table"
        ),

    Expansion1 =
        Table.ExpandTableColumn(
            Filtering,
            "Data",
            {"StartDate", "MaturityBand"}
        ),

    Grouping =
        Table.Group(
            Expansion1,
            "StartDate",
            {
                "nTable",
                each
                    Table.FromRows(
                        {[MaturityBand]},
                        [Item]
                    )
            }
        ),

    Cols = Table.Column(Filtering, "Item"),

    Expansion2 =
        Table.ExpandTableColumn(
            Grouping,
            "nTable",
            Cols
        ),

    Sorting =
        Table.Sort(
            Expansion2,
            {
                "StartDate",
                Order.Ascending
            }
        ),

    FillDown =
        Table.FillDown(
            Sorting,
            Cols
        ),

    DataType =
        Table.TransformColumnTypes(
            FillDown,
            {
                {"StartDate", type date},
                {"MaturityBand_LST", type text},
                {"MaturityBand_LCR", type text},
                {"MaturityBand_NSFR", type text}
            }
        )
in
    DataType

第一步,我们需要使用Excel.Workbook()和File.Contents()函数组合读取参数表所在的工作簿。为了在之后的步骤中能够利用第一步结果中的一些信息,参数表的展开需要使用Table.ExpandTableColumn()而不是常用的Table.Combine()。之后,需要使用Table.Group()实现以起始日为主键对展开的参数表进行分组,并使用Table.FromRows()对分组后的子表格进行枢纽化。在下一步中,再次使用Table.ExpandTableColumn()展开所有可能存在的列。由于在下一个左端点之前的每一天都属于当前区间,所以在最后一步需要对起始日进行升序排列并对所有在上一步中展开的列进行向下填充操作。合并好的日期参数表如下图所示:

附件

2 Replies to “合并日期参数表(二)”

  1. 透视表是不是更简单点
    let
    Path = "E:\office\PowerQuery系列课程\练习\简单\合并日期参数表(二)\Parameter-Table - 副本.xlsx",

    wkBook =
    Excel.Workbook(
    File.Contents(Path),
    true
    ),

    Filtering =
    Table.SelectRows(
    wkBook,
    each
    Text.StartsWith([Item], "MaturityBand")
    and
    [Kind] = "Table"
    ),

    Expansion1 =
    Table.ExpandTableColumn(
    Filtering,
    "Data",
    {"StartDate", "MaturityBand"}
    ),

    Expansion2 =
    Table.SelectColumns(
    Expansion1,
    {"StartDate","MaturityBand","Item" }
    ),

    Pivot1=Table.Pivot(
    Expansion2,List.Distinct(
    Expansion2 [Item]
    ),
    "Item","MaturityBand"
    ),

    cols= Table.Column(Filtering, "Item"),

    FillDown=Table.FillDown(Pivot1,cols),

    DataType =
    Table.TransformColumnTypes(
    FillDown,
    {
    {"StartDate", type date},
    {"MaturityBand_LST", type text},
    {"MaturityBand_LCR", type text},
    {"MaturityBand_NSFR", type text}
    }
    )
    in
    DataType

    1. 你提供的思路更简洁,这个套路工作上经常用到,谢谢。不过个人感觉还是要保守点,向下填充前的排序还是不要省去比较好。

发表回复

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