POWER查询中的局部组聚合

在查看PowerQuery库引用时，我注意到Table.Group()函数有一个非常有用的选项，可以使用它所称的本地组来聚合数据。与其解释它的作用，不如让我向您展示…

下表显示1月份的所有日子，以及2014年1月某一天雇员是否休假、上班或休病假：

将其导入POWER查询并查找用于每个活动的天数，使用按组电源查询窗口中的功能。下面是UI生成的脚本：

let

    Source = Excel.CurrentWorkbook(){[Name="Calendar"]}[Content],

    GroupedRows = Table.Group(Source, {"Activity"},

        {{"Count of Days", each Table.RowCount(_), type number}})

in

    GroupedRows

这是它产生的输出：

到目前一切尚好。但是，了解每一项活动的不同时间范围不是很有用吗？例如，您可以从第一张截图中看到，这位员工从1月17日(星期五)到1月21日(星期二)病假，然后又从1月24日(星期五)到1月27日(星期一)请病假；您可能希望看到这些员工被合并为两个不同的时间段。Group()还允许您这样做。

首先，下面是一个带有示例的脚本：

let

    Source = Excel.CurrentWorkbook(){[Name="Calendar"]}[Content],

    FilteredRows = Table.SelectRows(

                    Source

                    , each ([Day Of Week] <> "Saturday" and [Day Of Week] <> "Sunday")),

    TimeRanges = Table.Group(

                    FilteredRows

                    , "Activity"

                    , {

                        {"Start Date", each List.Min([Date]), type date}

                        , {"End Date", each List.Max([Date]), type date}

                        , {"Number of Days", each List.Count([Date]), type number}

                      }

                    , GroupKind.Local)

in

    TimeRanges

这是输出：

从这张截图中你可以看到，我现在连续每一天都有一行(不考虑周末)，不管是在度假、工作还是请病假。

一步一步地，下面是对脚本中所发生的事情的解释：

• 来源

  从工作表中的表中导入数据。

• 过滤柱

  过滤掉周末的日子

• 时间范围

  使用Table.group函数来完成所有有趣的事情：

• 它接受FilteredRows步骤返回的表

• 组是否在活动列上

• 它计算日期列的min、max和计数，并将它们添加为新列。

• 本地可选参数是获取您在这里看到的行为的关键。默认的分组类型GroupKind.Global通过整个表执行一个标准组，如上面的第一个示例所示。GroupKind.Local只在连续的行序列上进行聚合，这意味着我们看到了活动“工作”的三个不同的时间范围和“生病”的两个单独的组。