Share via

TripPin 第 10 部分 - 基本查詢折疊

  • 發行項

此多部分教學課程涵蓋如何建立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 系列將 OData 服務視為一般 REST API，使用 Web.Contents 而非 OData.Feed，因此您必須自行實作查詢折疊處理程式。 針對真實世界的使用量，建議您盡可能使用 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 的上述實作很簡單，但不太實用。 下列實作會作為您的基準使用—它不會實作任何折疊功能，但具有您需要執行的 Scaffolding。

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 此協助程式函式位於診斷課程模組中（在新增診斷課程中引進），並提供一個實用的方法來自動追蹤個別處理程式所引發的任何錯誤。

與 GetRows 函GetType式會更新為使用兩個新的協助程式函式，CalculateSchema 與 CalculateUrl。 現在，這些函式的實作相當簡單，請注意，它們包含函式先前完成 GetEntity 的部分。

最後，請注意，您正在定義可接受state參數的內部函式 （View）。 當您實作更多處理程式時，它們會遞歸地呼叫內部 View 函式、更新並隨著它們一起 state 傳遞。

再次更新函TripPinNavTable式，以對新TripPin.View函式的呼叫取代 對的呼叫TripPin.SuperSimpleView，然後重新執行單元測試。 您尚未看到任何新功能，但您現在有一個穩固的基準進行測試。

實作查詢折疊

由於 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 對等查詢語法、單元測試，以及實作。 使用先前所述的 Scaffolding 程式代碼，每個處理程式實作都需要兩個變更：

  • 將處理程式新增至更新記錄的 state Table.View。
  • 修改 CalculateUrl 以從 state 擷取值，並將 新增至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式會更新為從記錄擷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 從結果集中選取或移除數據行時，就會呼叫處理程式。 處理程式會 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 呼叫，以簡化測試案例。

注意

如果測試只是傳回數據行名稱（使用 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等於 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 查詢中，您有一些關於如何處理此值的不同選項：

查詢參數方法的缺點是，您仍然需要將整個查詢傳送至 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 體驗一律會在導覽器和查詢編輯器中顯示預覽時執行數據OnTake1000列，讓使用者在使用較大的數據集時可能會看到顯著的效能改善。