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

此多部分教程介绍如何为 Power Query 创建新的数据源扩展。 本教程旨在按顺序完成 — 每个课程都基于在上一课中创建的连接器上构建，以增量方式向连接器添加新功能。

在本课中，你将：

• $top
• $skip
• $count
• $select
• $orderby

M 语言的强大功能之一是能够将转换工作推送到一个或多个基础数据源。 此功能称为查询折叠，在其他工具或技术中，该功能也被称为谓词下推或查询委派。

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

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

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

有关查询折叠的详细信息，请转到 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 函数完成的部分。

最后，请注意，你正在定义接受参数的内部函数（View）。state 当你实现更多处理程序时，它们以递归方式调用内部 View 函数，并在更新和传递 state 的过程中继续进行。

将TripPin.SuperSimpleView的调用替换为新的TripPin.View函数，并再次更新TripPinNavTable函数，然后重新运行单元测试。 尚没有任何新功能，但现在有一个用于测试的坚实基线。

实现查询折叠

由于 M 引擎在无法折叠查询时自动回退到本地处理，因此必须执行一些额外的步骤来验证 Table.View 处理程序是否正常工作。

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

为了验证查询折叠的结果，可以采用一种自动化的方法：如果查询未完全折叠，则使单元测试执行失败。 若要使单元测试在查询未完全折叠时失败，请打开项目属性，并将 折叠失败时的错误 设置为 True。 启用此设置后，需要本地处理的任何查询都会导致以下错误：

We couldn't fold the expression to the source. Please try a simpler expression.

可以通过在包含一个或多个表转换的单元测试文件中新添加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 以从 state URL 和/或查询字符串参数中检索值并添加到 URL 和/或查询字符串参数。

使用 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函数将更新为从Top记录中提取state值，并在查询字符串中设置正确的参数。

// 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当用户从结果集中选择或删除列时，将调用处理程序。 处理程序接收list或text值，表示要选择的一列或多列。

在 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 调用以简化测试用例。

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

实现 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 等于 Order.Ascending 或 Order.Descending 的字段。

在 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 规范中所述，如果在路径段 /$count 中包含其他查询参数（例如 $top 或 $skip），将会返回错误，这限制了其用途。

在本教程中，你使用路径段方法实现了 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,

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

// 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 操作会回退到本地（默认）处理程序。 在设置折叠错误为“错误”的情况下运行测试会导致Table.RowCount失败，但允许测试成功。

结论

为连接器实现 Table.View 会大大增加代码的复杂性。 由于 M 引擎可以在本地处理所有转换，因此添加 Table.View 处理程序不会为用户提供新功能，但会导致更高效的处理（并且可能使用户更加满意）。 Table.View 处理程序是可选的一个主要优点是，它允许你以增量方式添加新功能，而不会影响连接器的向后兼容性。

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