Power Query 系列 (16) - List.Generate 函数用法

本篇讲解 List.Generate 函数的用法。这个函数的功能是用于生成 list，可以是单值，也可以是结构化类型，比较灵活，使用起来有一定难度。

List.Generate(
	initial as function, 
	condition as function, 
	next as function, 
	optional selector as nullable function) as list

4 个参数都是函数，参数的含义如下：

• initial: 通过这个函数构造一个单值或者结构化类型的数据，单值或者结构化类型的值作为结果 list 的第一项。第一个函数作为后面几个函数的参数。
• condition: 退出循环的条件。如果函数返回值为 false，则退出循环；如果函数的返回值为 true，将当前项加入到结果 list 中。接受第一个函数为参数。
• next：如何构造结果 list 下一项，该参数接受第一个函数为参数。
• selector：这是唯一一个可选的参数，提供将结果 list 进行改变的机制。如果不设置该参数，则第二个参数返回值为 false 时退出循环，将当前的结果 list 作为函数的返回值。

有一段 python 语法伪代码，可以让我们更好的理解函数的功能。这段代码来自：Loops in Power Query M language

def List.Generate(start, condition, next, transform=None):
    results = list()
    item = initial()
    while condition(item) == True:
        results.append(item)
        item = next(item)
    if selector is not None:
        output = list()
        for item in results:
            output.append(selector(item))
    else:
        output = results
    return output

还是觉得比较抽象吧。接下来通过一个例子来帮助我们理解。假设我们想输出数字 1 到 10，用 List.Generate 来实现。在 Power Query 中创建一个空查询，进入高级编辑器，在高级编辑器中输入下面的代码：

let
    Source = List.Generate(
        () => 1,
        (x) => x <= 10,
        (x) => x+1
    )
in
    Source

查询编辑器显示的结果如下：

对这个例子解释一下：

step 1: 通过函数构建一个单值数据 1
step 2: 将 1 传入第二个参数进行判断是否小于 10，因为 1 小于 10, 所以 1 被加入结果 list，即结果 list 为 {1}
step 3: 将 1 传入第三个参数，第三个参数执行 1 + 1 运算，结果变为 2
step 4: 将 2 传入第二个参数进行判断，执行后续的循环…

现在将示例修改一下，展示第 4 个参数的作用。

let
    Source = List.Generate(
        () => 1,
        (x) => x <= 10,
        (x) => x+1,
        (x) => "第 " & Text.From(x) & " 项"
    )
in
    Source

前面 3 个参数对单值 x 进行判断和改变，第 4 个参数构建一个字段串作为结果 list 进行输出。在查询编辑器中的显示如下：

List.Generate 的有用之处还是在于结构化类型的构建。基于我在参考部分列出的文章示例，我对文中的示例进行了改编，假设根据员工在不同 team 的异动记录，计算出在各 team 的起止日期，结束日期为在下个 team 的开始日期 - 1：

基于刚才对 List.Generate 的介绍，我们直接看 M 语言脚本代码：

let
    Source = Employees,
    InputData = Table.Sort(
      Source,{{"EmployeeName", Order.Ascending}, {"Date", Order.Ascending}}),

    DoReplace = List.Generate(
        ()=> [Employee="", Team="", StartDate=null,  EndDate=null, Counter=0],  
        each [Counter]<=Table.RowCount(InputData),                              // condition

        each [ 
            Employee=InputData{[Counter]}[EmployeeName], 
            Team=InputData{[Counter]}[Team],  
            StartDate = InputData{[Counter]}[Date],            
            EndDate =
                let 
                    CurrentRowEmployee = InputData{[Counter]}[EmployeeName],
                    NextRowEmployee = InputData{[Counter]+1}[EmployeeName],                  
                    NextRowDate = if NextRowEmployee = null then
                                     null
                                  else if CurrentRowEmployee = NextRowEmployee then
                                        try InputData{[Counter]+1}[Date] otherwise null
                                  else null,
                    MyDate = try Date.AddDays(NextRowDate, -1) otherwise null            
                in  
                    MyDate, 
            Counter=[Counter]+1], 

            // Our list will be a list of these records:
            each [[Employee], [Team], [StartDate], [EndDate]]     // transform
    ), // end of List.Generate
 
    // Convert our list of records into a table
    ConvertedtoTable = Table.FromList(
        DoReplace, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 
    // Expand it out 
    ExpandedColumn1 = Table.ExpandRecordColumn(
        ConvertedtoTable, 
        "Column1", 
        {"Employee", "Team", "StartDate", "EndDate"}, 
        {"Employee", "Team", "StartDate", "EndDate"}),
 
    // Remove nulls
    FilteredRows = Table.SelectRows(ExpandedColumn1, each ([StartDate] <> null))
in
    FilteredRows

• 设置 counter，循环 Table.RowCount(InputData) 次
• 第 1 个参数（initial）构造一个空的 record:

()=> [Employee="", Team="", StartDate=null,  EndDate=null, Counter=0]

• 循环的时候，每次构造一个 record 类型的对象：

[Employee = xxx, Team = xxx, StartDate = xxx, EndDate = xxx, Counter+1]

Counter 作为记数用，在第 4 个参数中，只保留有用的部分。

• 最后通过 Table.FromList 将 list 转换成 table，并对结构化列进行展开。

示例数据

github -List.Generate Demo.xlsx

参考

Fun with List.Generate