處理架構
根據您的數據源,可能或可能不會明確提供數據類型和數據行名稱的相關信息。 OData REST API 通常會使用 $metadata定義來處理此作業,而 Power Query OData.Feed
方法會自動處理剖析此資訊,並將它套用至從 OData 來源傳回的數據。
許多 REST API 無法以程式設計方式判斷其架構。 在這些情況下,您必須在連接器中包含架構定義。
最簡單的方法是將架構定義硬式編碼到您的連接器。 這足以用於大部分的使用案例。
整體而言,對連接器所傳回的數據強制執行架構具有多個優點,例如:
- 設定正確的數據類型。
- 拿掉不需要向使用者顯示的數據行(例如內部標識碼或狀態資訊)。
- 藉由新增回應中可能遺漏的任何數據行,確保每個數據頁都有相同的圖形(REST API 通常表示欄位應該完全省略字位為 Null)。
請考慮下列程式代碼,從 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
來示範手動硬式編碼架構的技術。
此資料表是結果:
您可以使用方便 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)
AirlineCode 和 Name 都是 any
類型。 Table.Schema
會傳回數據表中數據行的許多元數據,包括名稱、位置、類型資訊,以及許多進階屬性,例如 Precision、Scale 和 MaxLength。 目前,您應該只關心已刻有的類型 ()、基本類型 (TypeName
Kind
),以及數據行值是否可能是 Null (IsNullable
)。
您的架構資料表將由兩個資料列組成:
資料行 | 詳細資料 |
---|---|
名稱 | 資料行名稱。 這必須符合服務所傳回結果中的名稱。 |
類型 | 您要設定的 M 資料類型。 這可以是基本類型(text、number、datetime 等等),或已編列的類型(Int64.Type、Currency.Type 等等)。 |
資料表的 Airlines
硬式編碼架構數據表會將其 AirlineCode
和 Name
資料行設定為 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
有七個欄位,包括 list
s (Emails
、 AddressInfo
)、可為 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
以下所述的協助程式函式將用來強制執行數據上的架構。 它需要以下參數:
參數 | 類型 | Description |
---|---|---|
table | table | 您要強制執行架構的數據表。 |
schema | table | 要從中讀取數據行資訊的架構數據表,具有下列類型: type table [Name = text, Type = type] 。 |
enforceSchema | 數值 | (選擇性) 控制函式行為的列舉。 預設值 ( EnforceSchema.Strict = 1 ) 可確保輸出資料表符合新增任何遺漏資料行所提供的架構數據表,以及移除額外的數據行。 EnforceSchema.IgnoreExtraColumns = 2 選項可用來保留結果中的額外數據行。 使用 時 EnforceSchema.IgnoreMissingColumns = 3 ,將會忽略遺漏的數據行和額外的數據行。 |
此函式的邏輯看起來像這樣:
- 判斷源數據表中是否有任何遺漏的數據行。
- 判斷是否有任何額外的數據行。
- 忽略結構化資料列 (類型
list
為、record
和table
), 和資料行設定為 類型any
。 - 使用
Table.TransformColumnTypes
來設定每個數據行類型。 - 根據它們出現在架構數據表中的順序來重新排序數據行。
- 使用
Value.ReplaceType
設定數據表本身的類型。
注意
在檢視查詢編輯器中的結果時,設定數據表類型的最後一個步驟將會移除Power Query UI推斷類型資訊的需求,這有時可能會導致對API進行雙重呼叫。
在完整延伸模組的較大內容中,架構處理會在從 API 傳回數據表時進行。 這項功能通常會在分頁函式的最低層級進行(如果有的話),其中包含從導覽數據表傳遞的實體資訊。
由於許多分頁和導覽數據表的實作都是內容特定的,因此此處不會顯示實作硬式編碼架構處理機制的完整範例。 此 TripPin 範例 示範端對端解決方案的外觀。
上述硬式編碼實作會做好確保簡單 JSON 回應的架構保持一致,但僅限於剖析回應的第一層。 深度巢狀數據集會受益於下列方法,其利用 M 類型。
以下是語言規格中 M 語言類型的快速重新整理:
「類型值」是「分類」其他值的值。 由類型分類的值稱為「符合」該類型。 M 類型系統由下列類型種類組成:
- 基本型別,分類基本值(
binary
、、datetime
date
、、datetimezone
、duration
、logical
record
text
type
list
time
number
null
和)也包含一些抽象型別(function
、table
、any
和 )。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
參考 CityType
和 LocType
來表示其結構化數據行。
針對您想要表示為資料表的最上層實體,您可以定義 資料表類型:
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
先前的練習中使用的一樣。 不同於 SchemaTransformTable
,Table.ChangeType
會採用實際的 M 數據表類型做為自變數,並且會以遞歸方式套用所有巢狀類型的架構。 其簽章為:
Table.ChangeType = (table, tableType as type) as nullable table => ...
注意
為了彈性,函式可用於數據表和記錄清單(也就是數據表在 JSON 檔中的表示方式)。
接著,您必須更新連接器程式代碼,將 參數從 變更為 ,並將呼叫新增至 type
Table.ChangeType
schema
table
同樣地,這樣做的詳細數據非常具體,因此不值得在這裡詳細討論。 此擴充的 TripPin 連接器範例 示範端對端解決方案,實作這個更複雜的處理架構方法。