การจัดการเค้าร่าง
ทั้งนี้ขึ้นอยู่กับแหล่งข้อมูลของคุณ ข้อมูลเกี่ยวกับชนิดข้อมูลและชื่อคอลัมน์อาจหรืออาจไม่ได้รับการระบุไว้อย่างชัดเจน โดยทั่วไป OData REST API จะจัดการโดยใช้ ข้อกําหนด$metadata และเมธอด Power Query OData.Feed
จะจัดการการแยกวิเคราะห์ข้อมูลนี้และนําไปใช้กับข้อมูลที่ส่งกลับจาก แหล่งข้อมูล OData โดยอัตโนมัติ
REST API หลายตัวไม่มีวิธีการกําหนด Schema ของพวกเขาทางโปรแกรม ในกรณีเหล่านี้ คุณจะต้องรวมข้อกําหนด Schema ในตัวเชื่อมต่อของคุณ
วิธีที่ง่ายที่สุดคือการฮาร์ดโค้ดข้อกําหนด Schema ลงในตัวเชื่อมต่อของคุณ นี่เป็นเพียงพอสําหรับกรณีการใช้งานส่วนใหญ่
โดยรวมการบังคับใช้ Schema บนข้อมูลที่ส่งกลับโดยตัวเชื่อมต่อของคุณมีประโยชน์หลายอย่าง เช่น:
- การตั้งค่าชนิดข้อมูลที่ถูกต้อง
- การลบคอลัมน์ที่ไม่จําเป็นต้องแสดงต่อผู้ใช้ปลายทาง (เช่น รหัสภายในหรือข้อมูลสถานะ)
- ตรวจสอบให้แน่ใจว่าข้อมูลแต่ละหน้ามีรูปร่างเดียวกันโดยการเพิ่มคอลัมน์ใด ๆ ที่อาจขาดหายไปจากการตอบสนอง (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
การจัดการ Schema อัตโนมัติของฟังก์ชัน ในตัวอย่างนี้ คุณจะถือว่าแหล่งข้อมูลเป็น 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
แสดงเมตาดาต้าจํานวนมากเกี่ยวกับคอลัมน์ในตาราง รวมถึงชื่อ ตําแหน่ง ข้อมูลชนิด และคุณสมบัติขั้นสูงมากมาย เช่น ความแม่นยํา สเกล และ MaxLength ในตอนนี้ คุณควรคํานึงถึงตัวเองด้วยชนิดที่กําหนด (TypeName
) ชนิดแรกเริ่ม (Kind
) และค่าคอลัมน์อาจเป็น null (IsNullable
) หรือไม่
ตาราง Schema ของคุณจะประกอบด้วยสองคอลัมน์:
Column | รายละเอียด |
---|---|
ชื่อ | ชื่อของคอลัมน์ ซึ่งต้องตรงกับชื่อในผลลัพธ์ที่ส่งกลับโดยบริการ |
ขนิด | ชนิดข้อมูล M ที่คุณกําลังจะตั้งค่า ซึ่งอาจเป็นชนิดดั้งเดิม (ข้อความ ตัวเลข วันที่เวลา และอื่นๆ) หรือเป็นชนิดโดยกําเนิด (Int64.Type, Currency.Type และอื่นๆ) |
ตาราง Schema แบบฮาร์ดโค้ดสําหรับAirlines
ตารางจะตั้งค่าและName
คอลัมน์เป็น AirlineCode
text
และมีลักษณะดังนี้:
Airlines = #table({"Name", "Type"}, {
{"AirlineCode", type text},
{"Name", type text}
})
เมื่อคุณค้นหาจุดสิ้นสุดอื่น ๆ ให้พิจารณาตาราง Schema ต่อไปนี้:
ตาราง Airports
มีสี่เขตข้อมูลที่คุณจะต้องเก็บไว้ (รวมถึงหนึ่งในชนิด record
):
Airports = #table({"Name", "Type"}, {
{"IcaoCode", type text},
{"Name", type text},
{"IataCode", type text},
{"Location", type record}
})
ตาราง People
มีเจ็ดเขตข้อมูล รวมถึง list
s (Emails
, AddressInfo
), คอลัมน์ที่สามารถเป็นค่าว่างได้ (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
Schema หลักเดียวได้ :
SchemaTable = #table({"Entity", "SchemaTable"}, {
{"Airlines", Airlines},
{"Airports", Airports},
{"People", People}
})
ฟังก์ชันผู้ช่วยเหลือที่SchemaTransformTable
อธิบายไว้ด้านล่างจะถูกใช้เพื่อบังคับใช้ schema กับข้อมูลของคุณ ซึ่งจะใช้พารามิเตอร์ต่อไปนี้:
พารามิเตอร์ | ชนิด | คำอธิบาย |
---|---|---|
ตาราง | ตาราง | ตารางข้อมูลคุณจะต้องการบังคับใช้ Schema ของคุณ |
สคีมา | ตาราง | ตาราง Schema ที่จะอ่านข้อมูลคอลัมน์จากชนิดต่อไปนี้: type table [Name = text, Type = type] |
enforceSchema | ตัวเลข | (ไม่บังคับ) enum ที่ควบคุมลักษณะการทํางานของฟังก์ชัน ค่าเริ่มต้น ( EnforceSchema.Strict = 1 ) ช่วยให้แน่ใจว่าตารางผลลัพธ์จะตรงกับตาราง Schema ที่ให้ไว้โดยการเพิ่มคอลัมน์ที่ขาดหายไปและลบคอลัมน์เพิ่มเติมออก สามารถใช้ EnforceSchema.IgnoreExtraColumns = 2 ตัวเลือก เพื่อรักษาคอลัมน์เพิ่มเติมในผลลัพธ์ เมื่อใช้ EnforceSchema.IgnoreMissingColumns = 3 ทั้งคอลัมน์ที่ขาดหายไปและคอลัมน์เพิ่มเติมจะถูกละเว้น |
ตรรกะสําหรับฟังก์ชันนี้มีลักษณะดังนี้:
- พิจารณาว่ามีคอลัมน์ใดหายไปจากตารางต้นทางหรือไม่
- ตรวจสอบว่ามีคอลัมน์เพิ่มเติมใด ๆ หรือไม่
- ละเว้นคอลัมน์ที่มีโครงสร้าง (ของชนิด
list
,record
, และtable
) และคอลัมน์ที่ตั้งค่าเป็นชนิดany
- ใช้
Table.TransformColumnTypes
เพื่อตั้งค่าแต่ละชนิดคอลัมน์ - จัดลําดับคอลัมน์ใหม่ตามลําดับที่ปรากฏในตาราง Schema
- ตั้งค่าชนิดบนตารางเองโดยใช้
Value.ReplaceType
หมายเหตุ
ขั้นตอนสุดท้ายในการตั้งค่าชนิดตารางจะลบความจําเป็นสําหรับ UI ของ Power Query เพื่ออนุมานข้อมูลชนิดเมื่อดูผลลัพธ์ในตัวแก้ไขคิวรี ซึ่งในบางครั้งอาจส่งผลให้มีการเรียกไปยัง API สองครั้ง
ในบริบทที่มากขึ้นของส่วนขยายที่สมบูรณ์ การจัดการ Schema จะเกิดขึ้นเมื่อมีการส่งกลับตารางจาก API โดยทั่วไปแล้ว ฟังก์ชันนี้จะเกิดขึ้นที่ระดับต่ําสุดของฟังก์ชันการแบ่งหน้า (ถ้ามี) โดยมีข้อมูลเอนทิตีที่ส่งผ่านจากตารางนําทาง
เนื่องจากการใช้งานตารางการแบ่งหน้าและการนําทางจํานวนมากเป็นแบบเฉพาะบริบท ตัวอย่างที่สมบูรณ์ของการใช้กลไกการจัดการ Schema แบบฮาร์ดโค้ดจะไม่แสดงที่นี่ ตัวอย่าง TripPin นี้แสดงให้เห็นว่าโซลูชันแบบ end-to-end อาจมีลักษณะอย่างไร
การใช้งานแบบฮาร์ดโค้ดที่กล่าวถึงข้างต้นเป็นงานที่ดีในการตรวจสอบให้แน่ใจว่า Schema ยังคงสอดคล้องกันสําหรับการตอบสนอง JSON อย่างง่าย แต่ถูกจํากัดให้แยกวิเคราะห์ระดับแรกของการตอบสนอง ชุดข้อมูลที่ซ้อนกันลึกจะได้รับประโยชน์จากวิธีต่อไปนี้ ซึ่งใช้ประโยชน์จากชนิด M
นี่คือการรีเฟรชด่วนเกี่ยวกับชนิดในภาษา M จากข้อมูลจําเพาะของภาษา:
ค่าชนิด คือค่าที่ใช้ในการจัดประเภทค่าอื่น ๆ ค่าที่จัดประเภทตามชนิดจะกล่าวได้ว่า สอดคล้องกับ ชนิดนั้น ระบบชนิด M ประกอบด้วยชนิดของชนิดต่อไปนี้:
- ชนิด Primitive ซึ่งจัดประเภทค่าดั้งเดิม (
binary
, ,date
,datetime
,datetimezone
,duration
,list
,null
logical
,number
, ,record
,text
, ,time
)type
และยังรวมถึงจํานวนของชนิดนามธรรม (function
,table
,any
และnone
)- ชนิด Record ซึ่งจัดประเภทค่าเรกคอร์ดโดยยึดตามชื่อเขตข้อมูลและชนิดค่า
- ชนิด List ซึ่งจัดประเภทรายการโดยใช้ชนิดฐานของหน่วยข้อมูลเดียว
- ชนิดฟังก์ชัน ซึ่งจัดประเภทค่าฟังก์ชันที่ยึดตามชนิดของพารามิเตอร์และค่าผลลัพธ์
- ชนิด Table ซึ่งจัดประเภทค่าตารางโดยยึดตามชื่อคอลัมน์ ชนิดคอลัมน์ และคีย์
- ชนิด Nullable ซึ่งจัดประเภท null ของค่านอกเหนือจากค่าทั้งหมดที่จัดประเภทโดยชนิดฐาน
- ชนิด Type ซึ่งจัดประเภทค่าที่เป็นชนิด
เมื่อใช้เอาต์พุต 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
];
สังเกตว่าการอ้างอิง CityType
และ LocType
เพื่อแสดงคอลัมน์ที่มีโครงสร้างอย่างไรLocationType
สําหรับเอนทิตีระดับบนสุดที่คุณต้องการแสดงเป็นตาราง คุณสามารถกําหนด ชนิดตาราง:
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
) เพื่อบังคับใช้ Schema กับข้อมูลของคุณ เช่นเดียวกับที่คุณใช้ SchemaTransformTable
ในแบบฝึกหัดก่อนหน้านี้ ซึ่งแตกต่างจาก SchemaTransformTable
จะใช้ชนิดตาราง M จริงเป็นอาร์กิวเมนต์ และจะใช้ Schema ของคุณซ้ําสําหรับชนิดที่ซ้อนTable.ChangeType
กันทั้งหมด ลายเซ็นของมันคือ:
Table.ChangeType = (table, tableType as type) as nullable table => ...
หมายเหตุ
เพื่อความยืดหยุ่น ฟังก์ชันสามารถใช้ได้ในตารางตลอดจนรายการระเบียน (ซึ่งเป็นวิธีการแสดงตารางในเอกสาร JSON)
จากนั้นคุณจะต้องอัปเดตรหัสตัวเชื่อมต่อเพื่อเปลี่ยนschema
พารามิเตอร์จาก เป็น table
type
และเพิ่มการเรียกไปยังTable.ChangeType
อีกครั้งรายละเอียดสําหรับการทําเช่นนั้นเป็นการดําเนินการที่เฉพาะเจาะจงมากและดังนั้นจึงไม่คุ้มค่าที่จะลงในรายละเอียดที่นี่ ตัวอย่าง ตัวเชื่อมต่อ TripPin แบบขยายนี้แสดงให้เห็นถึงโซลูชันแบบ end-to-end ที่ใช้วิธีการที่ซับซ้อนมากขึ้นในการจัดการ schema