閱讀英文

共用方式為


處理架構

根據您的數據源,可能或可能不會明確提供數據類型和數據行名稱的相關信息。 OData REST API 通常會使用 $metadata定義來處理此作業,而 Power Query OData.Feed 方法會自動處理剖析此資訊,並將它套用至從 OData 來源傳回的數據。

許多 REST API 無法以程式設計方式判斷其架構。 在這些情況下,您必須在連接器中包含架構定義。

簡單硬式編碼方法

最簡單的方法是將架構定義硬式編碼到您的連接器。 這足以用於大部分的使用案例。

整體而言,對連接器所傳回的數據強制執行架構具有多個優點,例如:

  • 設定正確的數據類型。
  • 拿掉不需要向使用者顯示的數據行(例如內部標識碼或狀態資訊)。
  • 藉由新增回應中可能遺漏的任何數據行,確保每個數據頁都有相同的圖形(REST API 通常表示欄位應該完全省略字位為 Null)。

使用 檢視現有的架構 Table.Schema

請考慮下列程式代碼,從 TripPin OData 範例服務傳回簡單的資料表:

let
    url = "https://services.odata.org/TripPinWebApiService/Airlines",
    source = Json.Document(Web.Contents(url))[value],
    asTable = Table.FromRecords(source)
in
    asTable

注意

TripPin 是 OData 來源,因此實際來說,只要使用 OData.Feed 函式的自動架構處理,就更有意義。 在此範例中,您會將來源視為一般的 REST API,並使用 Web.Contents 來示範手動硬式編碼架構的技術。

此資料表是結果:

TripPin Airline 數據的數據表。

您可以使用方便 Table.Schema 的函式來檢查資料行的資料類型:

let
    url = "https://services.odata.org/TripPinWebApiService/Airlines",
    source = Json.Document(Web.Contents(url))[value],
    asTable = Table.FromRecords(source)
in
    Table.Schema(asTable)

已套用至 TripPin Airline 數據之 Table.Schema 的結果。

AirlineCode 和 Name 都是 any 類型。 Table.Schema 會傳回數據表中數據行的許多元數據,包括名稱、位置、類型資訊,以及許多進階屬性,例如 Precision、Scale 和 MaxLength。 目前,您應該只關心已刻有的類型 ()、基本類型 (TypeNameKind),以及數據行值是否可能是 Null (IsNullable)。

定義簡單的架構數據表

您的架構資料表將由兩個資料列組成:

資料行 詳細資料
名稱 資料行名稱。 這必須符合服務所傳回結果中的名稱。
類型 您要設定的 M 資料類型。 這可以是基本類型(text、number、datetime 等等),或已編列的類型(Int64.Type、Currency.Type 等等)。

資料表的 Airlines 硬式編碼架構數據表會將其 AirlineCodeName 資料行設定為 text ,如下所示:

Airlines = #table({"Name", "Type"}, {
        {"AirlineCode", type text},
        {"Name", type text}
    })

當您查看其他一些端點時,請考慮下列架構資料表:

資料表 Airports 有四個您想要保留的欄位(包括其中一種類型 record):

Airports = #table({"Name", "Type"}, {
        {"IcaoCode", type text},
        {"Name", type text},
        {"IataCode", type text},
        {"Location", type record}
    })

資料表People有七個欄位,包括 lists (EmailsAddressInfo)、可為 Null 的數據行 (Gender),以及具有已標寫類型的資料行 (Concurrency):

People = #table({"Name", "Type"}, {
        {"UserName", type text},
        {"FirstName", type text},
        {"LastName", type text},
        {"Emails", type list},
        {"AddressInfo", type list},
        {"Gender", type nullable text},
        {"Concurrency", Int64.Type}
    })

您可以將所有這些資料表放入單一主要架構資料表 SchemaTable中:

SchemaTable = #table({"Entity", "SchemaTable"}, {
        {"Airlines", Airlines},
        {"Airports", Airports},
        {"People", People}
    })

架構數據表。

SchemaTransformTable 協助程式函式

SchemaTransformTable 以下所述的協助程式函式將用來強制執行數據上的架構。 它需要以下參數:

參數 類型 Description
table table 您要強制執行架構的數據表。
schema table 要從中讀取數據行資訊的架構數據表,具有下列類型: type table [Name = text, Type = type]
enforceSchema 數值 選擇性) 控制函式行為的列舉。
預設值 (EnforceSchema.Strict = 1) 可確保輸出資料表符合新增任何遺漏資料行所提供的架構數據表,以及移除額外的數據行。
EnforceSchema.IgnoreExtraColumns = 2選項可用來保留結果中的額外數據行。
使用 時 EnforceSchema.IgnoreMissingColumns = 3 ,將會忽略遺漏的數據行和額外的數據行。

此函式的邏輯看起來像這樣:

  1. 判斷源數據表中是否有任何遺漏的數據行。
  2. 判斷是否有任何額外的數據行。
  3. 忽略結構化資料列 (類型 list為、 recordtable), 和資料行設定為 類型 any
  4. 使用 Table.TransformColumnTypes 來設定每個數據行類型。
  5. 根據它們出現在架構數據表中的順序來重新排序數據行。
  6. 使用 Value.ReplaceType設定數據表本身的類型。

注意

在檢視查詢編輯器中的結果時,設定數據表類型的最後一個步驟將會移除Power Query UI推斷類型資訊的需求,這有時可能會導致對API進行雙重呼叫。

融會貫通

在完整延伸模組的較大內容中,架構處理會在從 API 傳回數據表時進行。 這項功能通常會在分頁函式的最低層級進行(如果有的話),其中包含從導覽數據表傳遞的實體資訊。

由於許多分頁和導覽數據表的實作都是內容特定的,因此此處不會顯示實作硬式編碼架構處理機制的完整範例。 此 TripPin 範例 示範端對端解決方案的外觀。

複雜的方法

上述硬式編碼實作會做好確保簡單 JSON 回應的架構保持一致,但僅限於剖析回應的第一層。 深度巢狀數據集會受益於下列方法,其利用 M 類型。

以下是語言規格中 M 語言類型的快速重新整理:

「類型值」是「分類」其他值的值。 由類型分類的值稱為「符合」該類型。 M 類型系統由下列類型種類組成:

  • 基本型別,分類基本值(binary、、datetimedate、、datetimezonedurationlogicalrecordtexttypelisttimenumbernull和)也包含一些抽象型別(functiontableany和 )。none
  • 記錄類型,其會根據功能變數名稱和實值類型來分類記錄值。
  • 清單類型,會使用單一專案基底類型來分類清單。
  • 函式類型,會根據其參數的類型和傳回值來分類函式值。
  • 數據表類型,會根據數據行名稱、數據行類型和索引鍵來分類數據表值。
  • 可為 Null 的類型,除了基底類型分類的所有值之外,也會分類 null 值。
  • 類型類型,可分類屬於型別的值。

使用您取得的原始 JSON 輸出(以及/或藉由查閱服務$metadata中的定義),您可以定義下列記錄類型來表示 OData 複雜類型:

LocationType = type [
    Address = text,
    City = CityType,
    Loc = LocType
];

CityType = type [
    CountryRegion = text,
    Name = text,
    Region = text
];

LocType = type [
    #"type" = text,
    coordinates = {number},
    crs = CrsType
];

CrsType = type [
    #"type" = text,
    properties = record
];

請注意 ,如何 LocationType 參考 CityTypeLocType 來表示其結構化數據行。

針對您想要表示為資料表的最上層實體,您可以定義 資料表類型

AirlinesType = type table [
    AirlineCode = text,
    Name = text
];
AirportsType = type table [
    Name = text,
    IataCode = text,
    Location = LocationType
];
PeopleType = type table [
    UserName = text,
    FirstName = text,
    LastName = text,
    Emails = {text},
    AddressInfo = {nullable LocationType},
    Gender = nullable text,
    Concurrency  Int64.Type
];

然後,您可以更新 SchemaTable 變數(您可以做為實體對類型對應的查閱表),以使用這些新的類型定義:

SchemaTable = #table({"Entity", "Type"}, {
    {"Airlines", AirlinesType},
    {"Airports", AirportsType},
    {"People", PeopleType}
});

您可以依賴一般函式 (Table.ChangeType) 來對數據強制執行架構,就像您在 SchemaTransformTable 先前的練習中使用的一樣。 不同於 SchemaTransformTableTable.ChangeType會採用實際的 M 數據表類型做為自變數,並且會以遞歸方式套用所有巢狀類型的架構。 其簽章為:

Table.ChangeType = (table, tableType as type) as nullable table => ...

注意

為了彈性,函式可用於數據表和記錄清單(也就是數據表在 JSON 檔中的表示方式)。

接著,您必須更新連接器程式代碼,將 參數從 變更為 ,並將呼叫新增至 typeTable.ChangeTypeschema table 同樣地,這樣做的詳細數據非常具體,因此不值得在這裡詳細討論。 此擴充的 TripPin 連接器範例 示範端對端解決方案,實作這個更複雜的處理架構方法。