TripPin 第 10 部分 - 基本查询折叠

项目
05/17/2024

注意

此内容当前引用了 Visual Studio 中用于日志的旧实现中的内容。内容将在不久的将来更新，以涵盖 Visual Studio Code 中新的 Power Query SDK。

本教程分为多个部分，介绍如何针对 Power Query 创建新数据源扩展。本教程按顺序进行，每一课都建立在前几课创建的连接器的基础上，逐步为连接器添加新功能。

在本课中，你将：

了解查询折叠的基础知识
了解 Table.View 函数
复制 OData 查询折叠处理程序，用于以下目的：

$top
$skip
$count
$select
$orderby

M 语言的强大功能之一是能够将转换工作推送到一个或多个基础数据源。此功能称为查询折叠（其他工具/技术也将类似功能称为谓词下推或查询委托）。

在创建使用内置查询折叠功能的 M 函数（如 OData.Feed 或 Odbc.DataSource）的自定义连接器时，连接器将自动免费继承此功能。

本教程将通过实现 Table.View 函数的函数处理程序来复制 OData 的内置查询折叠行为。本教程的这一部分将实现一些较容易实现的处理程序（即不需要表达式分析和状态跟踪的处理程序）。

若要详细了解 OData 服务可能提供的查询功能，请转到 OData v4 URL 约定。

注意

如上所述，OData.Feed 函数将自动提供查询折叠功能。由于 TripPin 系列使用 Web.Contents 而非 OData.Feed 将 OData 服务视为常规 REST API，因此需要自行实现查询折叠处理程序。在实际使用中，建议尽可能使用 OData.Feed。

有关查询折叠的详细信息，请转到 Power Query 中的查询评估和查询折叠概述。

使用 Table.View

Table.View 函数允许自定义连接器覆盖数据源的默认转换处理程序。 Table.View 的实现将为一个或多个支持的处理程序提供函数。如果处理程序未实现或在评估过程中返回 error，M 引擎将返回默认处理程序。

当自定义连接器使用不支持隐式查询折叠的函数（如 Web.Contents）时，默认转换处理程序将始终在本地执行。如果要连接到的 REST API 支持将查询参数作为查询的一部分，Table.View 可让你添加优化功能，以便将转换工作推送到服务。

Table.View 函数签名如下：

Table.View(table as nullable table, handlers as record) as table

你的实现将包装主数据源函数。 Table.View 有两个必需处理程序：

GetType- 返回查询结果的预期值 table type
GetRows- 返回数据源函数的实际 table 结果

最简单的实现方法与下方示例类似：

TripPin.SuperSimpleView = (url as text, entity as text) as table =>
    Table.View(null, [
        GetType = () => Value.Type(GetRows()),
        GetRows = () => GetEntity(url, entity)
    ]);

将 TripPinNavTable 函数更新为调用 TripPin.SuperSimpleView 而不是 GetEntity：

withData = Table.AddColumn(rename, "Data", each TripPin.SuperSimpleView(url, [Name]), type table),

如果重新运行单元测试，就会发现函数的行为并没有改变。在这种情况下，Table.View 实现只是通过调用传递给 GetEntity。由于（尚未）实现任何转换处理程序，因此原始 url 参数保持不变。

Table.View 的初始实现

Table.View 的上述实现很简单，但用处不大。以下实现将用作基线，它不实现任何折叠功能，但提供了实现折叠功能所需的基架。

TripPin.View = (baseUrl as text, entity as text) as table =>
    let
        // Implementation of Table.View handlers.
        //
        // We wrap the record with Diagnostics.WrapHandlers() to get some automatic
        // tracing if a handler returns an error.
        //
        View = (state as record) => Table.View(null, Diagnostics.WrapHandlers([
            // Returns the table type returned by GetRows()
            GetType = () => CalculateSchema(state),

            // Called last - retrieves the data from the calculated URL
            GetRows = () => 
                let
                    finalSchema = CalculateSchema(state),
                    finalUrl = CalculateUrl(state),

                    result = TripPin.Feed(finalUrl, finalSchema),
                    appliedType = Table.ChangeType(result, finalSchema)
                in
                    appliedType,

            //
            // Helper functions
            //
            // Retrieves the cached schema. If this is the first call
            // to CalculateSchema, the table type is calculated based on
            // the entity name that was passed into the function.
            CalculateSchema = (state) as type =>
                if (state[Schema]? = null) then
                    GetSchemaForEntity(entity)
                else
                    state[Schema],

            // Calculates the final URL based on the current state.
            CalculateUrl = (state) as text => 
                let
                    urlWithEntity = Uri.Combine(state[Url], state[Entity])
                in
                    urlWithEntity
        ]))
    in
        View([Url = baseUrl, Entity = entity]);

如果查看对 Table.View 的调用，则会发现在记录 handlers 周围存在额外包装函数 Diagnostics.WrapHandlers。此帮助程序函数位于诊断模块（在添加诊断课程中引入）中，并提供一种有用的方法来自动跟踪单个处理程序引发的任何错误。

GetType 和 GetRows 函数已更新，因而可使用两个新的帮助程序函数 CalculateSchema 和 CalculateUrl。现在，这些函数的实现相当简单明了，你会发现它们包含以前由 GetEntity 函数完成的部分。

最后，你会注意到你正在定义接受 state 参数的内部函数 (View)。当你实现更多处理程序时，它们将以递归方式调用内部 View 函数，并在调用过程中更新和传递 state。

再次更新 TripPinNavTable 函数，将调用 TripPin.SuperSimpleView 替换为调用新 TripPin.View 函数，然后重新运行单元测试。虽然还无法看到任何新功能，但你现在已拥有用于测试的坚实基线。

实现查询折叠

M 引擎会在查询无法折叠时自动返回本地处理，因此必须采取一些其他步骤来验证 Table.View 处理程序是否正常工作。

手动验证折叠行为的方法是使用 Fiddler 等工具监视单元测试发出的 URL 请求。或者，你添加到 TripPin.Feed 的诊断日志会发出要运行的完整 URL，其中应包括处理程序将添加的 OData 查询字符串参数。

验证查询折叠的自动化方法是，如果查询未完全折叠，则强制单元测试执行失败。为此，可以打开项目属性，将“折叠失败时出错”设置为 True。启用此设置后，任何需要本地处理的查询都会出现以下错误：

我们无法将表达式折叠到源。请尝试更简单的表达式。

可以在包含一个或多个表转换的单元测试文件中添加一个新的 Fact 来测试此功能。

// Query folding tests
Fact("Fold $top 1 on Airlines", 
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ), 
    Table.FirstN(Airlines, 1)
)

注意

“折叠失败时出错”设置是一种“全有或全无”的方法。如果想在单元测试中测试未设计为折叠的查询，则需要添加一些条件逻辑来相应启用/禁用测试。

本教程的其余部分将分别添加一个新的 Table.View 处理程序。你将采用测试驱动开发 (TDD) 方法，首先添加失败的单元测试，然后执行 M 代码来解决这些问题。

下面的每个处理程序部分都将介绍处理程序提供的功能、OData 等效查询语法、单元测试和实现。使用上述基架代码，每个处理程序的实现都需要进行两处更改：

在 Table.View 中添加处理程序以更新 state 记录。
修改 CalculateUrl 以从 URL 和/或查询字符串参数中检索 state 值并添加到这些参数中。

使用 OnTake 处理 Table.FirstN

处理程序 OnTake 接收参数 count，即从 GetRows 中获取记录的最大行数。用 OData 术语来说，可以将此项转换为 $top 查询参数。

你将使用以下单元测试：

// Query folding tests
Fact("Fold $top 1 on Airlines", 
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ), 
    Table.FirstN(Airlines, 1)
),
Fact("Fold $top 0 on Airports", 
    #table( type table [Name = text, IataCode = text, Location = record] , {} ), 
    Table.FirstN(Airports, 0)
),

这些测试都使用 Table.FirstN 筛选结果集的前 X 行数。如果运行这些测试时将“折叠失败时出错”设置为 False（默认），测试应该会成功，但如果运行 Fiddler（或检查跟踪日志），你会发现发送的请求不包含任何 OData 查询参数。

诊断跟踪。

如果将“折叠失败时出错”设置为 True，测试将失败并显示 Please try a simpler expression. 错误。若要解决此问题，请为 OnTake 定义第一个 Table.View 处理程序。

OnTake 处理程序类似于以下代码：

OnTake = (count as number) =>
    let
        // Add a record with Top defined to our state
        newState = state & [ Top = count ]
    in
        @View(newState),

更新函数 CalculateUrl 以从 state 记录中提取值 Top，并在查询字符串中设置正确的参数。

// Calculates the final URL based on the current state.
CalculateUrl = (state) as text => 
    let
        urlWithEntity = Uri.Combine(state[Url], state[Entity]),

        // Uri.BuildQueryString requires that all field values
        // are text literals.
        defaultQueryString = [],

        // Check for Top defined in our state
        qsWithTop =
            if (state[Top]? <> null) then
                // add a $top field to the query string record
                defaultQueryString & [ #"$top" = Number.ToText(state[Top]) ]
            else
                defaultQueryString,

        encodedQueryString = Uri.BuildQueryString(qsWithTop),
        finalUrl = urlWithEntity & "?" & encodedQueryString
    in
        finalUrl

重新运行单元测试时，你会发现访问的 URL 现在包含参数 $top。由于 URL 编码的原因，$top 显示为 %24top，但 OData 服务足够智能，可以自动进行转换。

具有上限数量的诊断跟踪。

使用 OnSkip 处理 Table.Skip

处理程序 OnSkip 与 OnTake 非常类似。它接收参数 count，即要从结果集中跳过的行数。此处理程序可以很好地转换为 OData $skip 查询参数。

单元测试：

// OnSkip
Fact("Fold $skip 14 on Airlines",
    #table( type table [AirlineCode = text, Name = text] , {{"EK", "Emirates"}} ), 
    Table.Skip(Airlines, 14)
),
Fact("Fold $skip 0 and $top 1",
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ),
    Table.FirstN(Table.Skip(Airlines, 0), 1)
),

实现：

// OnSkip - handles the Table.Skip transform.
// The count value should be >= 0.
OnSkip = (count as number) =>
    let
        newState = state & [ Skip = count ]
    in
        @View(newState),

匹配更新到 CalculateUrl：

qsWithSkip = 
    if (state[Skip]? <> null) then
        qsWithTop & [ #"$skip" = Number.ToText(state[Skip]) ]
    else
        qsWithTop,

详情请参见：Table.Skip

使用 OnSelectColumns 处理 Table.SelectColumns

当用户从结果集中选择或删除列时，将调用处理程序 OnSelectColumns。处理程序接收 text 值的 list，代表要选择的一列或多列。

在 OData 术语中，此操作将映射到 $select 查询选项。

在处理包含许多列的表时，折叠列选择的优势就显而易见了。运算符 $select 将从结果集中删除未选择的列，从而提高查询效率。

单元测试：

// OnSelectColumns
Fact("Fold $select single column", 
    #table( type table [AirlineCode = text] , {{"AA"}} ),
    Table.FirstN(Table.SelectColumns(Airlines, {"AirlineCode"}), 1)
),
Fact("Fold $select multiple column", 
    #table( type table [UserName = text, FirstName = text, LastName = text],{{"russellwhyte", "Russell", "Whyte"}}), 
    Table.FirstN(Table.SelectColumns(People, {"UserName", "FirstName", "LastName"}), 1)
),
Fact("Fold $select with ignore column", 
    #table( type table [AirlineCode = text] , {{"AA"}} ),
    Table.FirstN(Table.SelectColumns(Airlines, {"AirlineCode", "DoesNotExist"}, MissingField.Ignore), 1)
),

前两个测试使用 Table.SelectColumns 选择不同的列数，并包含 Table.FirstN 调用以简化测试用例。

注意

如果测试仅返回列名称（使用 Table.ColumnNames 而不是任何数据），则向 OData 服务发出的请求永远不会实际发送。这是因为调用 GetType 将返回架构，其中包含 M 引擎计算结果所需的所有信息。

第三个测试使用 MissingField.Ignore 选项，指示 M 引擎忽略结果集中不存在的任何选定列。处理程序 OnSelectColumns 无需担心此选项，M 引擎会自动处理（也就是说，缺失的列不会包含在 columns 列表中）。

注意

Table.SelectColumns 的另一个选项 MissingField.UseNull 需要连接器来实现 OnAddColumn 处理程序。这将在后续课程中完成。

实现 OnSelectColumns 有两项目的：

将所选列的列表添加到 state 中。
重新计算 Schema 值，以便设置正确的表类型。

OnSelectColumns = (columns as list) =>
    let
        // get the current schema
        currentSchema = CalculateSchema(state),
        // get the columns from the current schema (which is an M Type value)
        rowRecordType = Type.RecordFields(Type.TableRow(currentSchema)),
        existingColumns = Record.FieldNames(rowRecordType),
        // calculate the new schema
        columnsToRemove = List.Difference(existingColumns, columns),
        updatedColumns = Record.RemoveFields(rowRecordType, columnsToRemove),
        newSchema = type table (Type.ForRecord(updatedColumns, false))
    in
        @View(state & 
            [ 
                SelectColumns = columns,
                Schema = newSchema
            ]
        ),

更新 CalculateUrl 为从状态检索列列表，并将它们（带分隔符）合并为 $select 参数。

// Check for explicitly selected columns
qsWithSelect =
    if (state[SelectColumns]? <> null) then
        qsWithSkip & [ #"$select" = Text.Combine(state[SelectColumns], ",") ]
    else
        qsWithSkip,

使用 OnSort 处理 Table.Sort

处理程序 OnSort 接收类型为记录的列表：

type [ Name = text, Order = Int16.Type ]

每个记录都包含一个表示列名称的 Name 字段，以及一个等于 Order.Ascending 或 Order.Descending 的 Order 字段。

在 OData 术语中，此操作将映射到 $orderby 查询选项。语法 $orderby 是在列名后加上 asc 或 desc，表示升序或降序。对多个列进行排序时，值之间用逗号隔开。如果 columns 参数包含多个项，请务必保持它们显示的顺序。

单元测试：

// OnSort
Fact("Fold $orderby single column",
    #table( type table [AirlineCode = text, Name = text], {{"TK", "Turkish Airlines"}}),
    Table.FirstN(Table.Sort(Airlines, {{"AirlineCode", Order.Descending}}), 1)
),
Fact("Fold $orderby multiple column",
    #table( type table [UserName = text], {{"javieralfred"}}),
    Table.SelectColumns(Table.FirstN(Table.Sort(People, {{"LastName", Order.Ascending}, {"UserName", Order.Descending}}), 1), {"UserName"})
)

实现：

// OnSort - receives a list of records containing two fields: 
//    [Name]  - the name of the column to sort on
//    [Order] - equal to Order.Ascending or Order.Descending
// If there are multiple records, the sort order must be maintained.
//
// OData allows you to sort on columns that do not appear in the result
// set, so we do not have to validate that the sorted columns are in our 
// existing schema.
OnSort = (order as list) =>
    let
        // This will convert the list of records to a list of text,
        // where each entry is "<columnName> <asc|desc>"
        sorting = List.Transform(order, (o) => 
            let
                column = o[Name],
                order = o[Order],
                orderText = if (order = Order.Ascending) then "asc" else "desc"
            in
                column & " " & orderText
        ),
        orderBy = Text.Combine(sorting, ", ")
    in
        @View(state & [ OrderBy = orderBy ]),

更新至 CalculateUrl：

qsWithOrderBy = 
    if (state[OrderBy]? <> null) then
        qsWithSelect & [ #"$orderby" = state[OrderBy] ]
    else
        qsWithSelect,

使用 GetRowCount 处理 Table.RowCount

与正在实现的其他查询处理程序不同，处理程序 GetRowCount 将返回单个值，即结果集中的预期行数。在 M 查询中，此值通常是 Table.RowCount 转换的结果。

有关在 OData 查询中处理此值的几种不同方式：

$count 查询参数，在结果集中作为单独字段返回计数。
/$count 路径段，只会以标量值的形式返回总计数。

查询参数方法的缺点是，仍需将整个查询发送到 OData 服务。由于计数会作为结果集的一部分内联返回，所以必须处理结果集中的第一页数据。虽然这比读取整个结果集并计算行数更有效率，但工作量可能还是会超出预期。

路径段方法的优点是，只会在结果中收到单个标量值。这个方法使整个操作更加高效。不过，如 OData 规范中所述，如果包含其他查询参数（例如 $top 或 $skip），则 /$count 路径段将返回错误，这限制了它的用途。

本教程将使用路径段方法实现 GetRowCount 处理程序。若要避免包含其他查询参数时出现错误，请检查其他状态值，如果发现任何状态值，则返回“未实现错误”(...)。从 Table.View 处理程序返回任何错误都会告知 M 引擎，该操作无法折叠，因此应回退到默认处理程序（在本例中就是计算总行数）。

首先，添加简单的单元测试：

// GetRowCount
Fact("Fold $count", 15, Table.RowCount(Airlines)),

/$count 路径段返回单个值（纯文本格式）而不是 JSON 结果集，因此还必须添加新的内部函数 (TripPin.Scalar) 用于发出请求和处理结果。

// Similar to TripPin.Feed, but is expecting back a scalar value.
// This function returns the value from the service as plain text.
TripPin.Scalar = (url as text) as text =>
    let
        _url = Diagnostics.LogValue("TripPin.Scalar url", url),

        headers = DefaultRequestHeaders & [
            #"Accept" = "text/plain"
        ],

        response = Web.Contents(_url, [ Headers = headers ]),
        toText = Text.FromBinary(response)
    in
        toText;

如果在 state 中未找到其他查询参数，该实现将使用此函数：

GetRowCount = () as number =>
    if (Record.FieldCount(Record.RemoveFields(state, {"Url", "Entity", "Schema"}, MissingField.Ignore)) > 0) then
        ...
    else
        let
            newState = state & [ RowCountOnly = true ],
            finalUrl = CalculateUrl(newState),
            value = TripPin.Scalar(finalUrl),
            converted = Number.FromText(value)
        in
            converted,

如果字段 RowCountOnly 在 state 中设置，则 CalculateUrl 函数将更新为追加 /$count 到 URL。

// Check for $count. If all we want is a row count,
// then we add /$count to the path value (following the entity name).
urlWithRowCount =
    if (state[RowCountOnly]? = true) then
        urlWithEntity & "/$count"
    else
        urlWithEntity,

新的 Table.RowCount 单元测试现在应通过了。

为了测试回退情况，将添加另一个强制出错的测试。

首先，添加一个帮助程序方法，用于检查 try 操作结果是否存在折叠错误。

// Returns true if there is a folding error, or the original record (for logging purposes) if not.
Test.IsFoldingError = (tryResult as record) =>
    if ( tryResult[HasError]? = true and tryResult[Error][Message] = "We couldn't fold the expression to the data source. Please try a simpler expression.") then
        true
    else
        tryResult;

然后添加一个使用 Table.RowCount 和 Table.FirstN 的测试，进行强制出错。

// test will fail if "Fail on Folding Error" is set to false
Fact("Fold $count + $top *error*", true, Test.IsFoldingError(try Table.RowCount(Table.FirstN(Airlines, 3)))),

这里需要注意的是，如果将“折叠错误时出错”设置为 false，此测试现在将返回错误，因为 Table.RowCount 操作将回退到本地（默认）处理程序。如果将“折叠错误时出错”设置为 true，运行测试将导致 Table.RowCount 失败，并允许测试成功。

结束语

为连接器实现 Table.View 会大大增加代码的复杂性。 M 引擎可以在本地处理所有转换，因此添加 Table.View 处理程序并不会为用户启用新方案，但会带来更高效的处理（以及还可能让用户更满意）。 Table.View 处理程序是可选的，其主要优点之一是可以逐步添加新功能，而不会影响连接器的向后兼容性。

对于大多数连接器来说，要实现的重要（和基本）处理程序是 OnTake（在 OData 中转换为 $top），因为它限制了返回的行数。在导航器和查询编辑器中显示预览时，Power Query 体验将始终执行 1000 行的 OnTake，因此用户在处理大型数据集时可能会看到显著的性能提升。

通过

TripPin 第 10 部分 - 基本查询折叠

使用 Table.View

Table.View 的初始实现

实现查询折叠

使用 OnTake 处理 Table.FirstN

使用 OnSkip 处理 Table.Skip

使用 OnSelectColumns 处理 Table.SelectColumns

使用 OnSort 处理 Table.Sort

使用 GetRowCount 处理 Table.RowCount

结束语

反馈

其他资源