การจัดการเค้าร่าง

  • บทความ

ทั้งนี้ขึ้นอยู่กับแหล่งข้อมูลของคุณ ข้อมูลเกี่ยวกับชนิดข้อมูลและชื่อคอลัมน์อาจหรืออาจไม่ได้รับการระบุไว้อย่างชัดเจน โดยทั่วไป OData REST API จะจัดการโดยใช้ ข้อกําหนด$metadata และเมธอด Power Query OData.Feed จะจัดการการแยกวิเคราะห์ข้อมูลนี้และนําไปใช้กับข้อมูลที่ส่งกลับจาก แหล่งข้อมูล OData โดยอัตโนมัติ

REST API หลายตัวไม่มีวิธีการกําหนด Schema ของพวกเขาทางโปรแกรม ในกรณีเหล่านี้ คุณจะต้องรวมข้อกําหนด Schema ในตัวเชื่อมต่อของคุณ

วิธี hardcoded อย่างง่าย

วิธีที่ง่ายที่สุดคือการฮาร์ดโค้ดข้อกําหนด Schema ลงในตัวเชื่อมต่อของคุณ นี่เป็นเพียงพอสําหรับกรณีการใช้งานส่วนใหญ่

โดยรวมการบังคับใช้ Schema บนข้อมูลที่ส่งกลับโดยตัวเชื่อมต่อของคุณมีประโยชน์หลายอย่าง เช่น:

  • การตั้งค่าชนิดข้อมูลที่ถูกต้อง
  • การลบคอลัมน์ที่ไม่จําเป็นต้องแสดงต่อผู้ใช้ปลายทาง (เช่น รหัสภายในหรือข้อมูลสถานะ)
  • ตรวจสอบให้แน่ใจว่าข้อมูลแต่ละหน้ามีรูปร่างเดียวกันโดยการเพิ่มคอลัมน์ใด ๆ ที่อาจขาดหายไปจากการตอบสนอง (REST API โดยทั่วไปจะระบุว่าเขตข้อมูลควรเป็น null โดยการละเว้นทั้งหมด)

การดู schema ที่มีอยู่ด้วย 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 การจัดการ Schema อัตโนมัติของฟังก์ชัน ในตัวอย่างนี้ คุณจะถือว่าแหล่งข้อมูลเป็น 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)

ผลลัพธ์ของ Table.Schema ที่นําไปใช้กับข้อมูล TripPin Airline

ทั้ง AirlineCode และ Name เป็น any ชนิด Table.Schema แสดงเมตาดาต้าจํานวนมากเกี่ยวกับคอลัมน์ในตาราง รวมถึงชื่อ ตําแหน่ง ข้อมูลชนิด และคุณสมบัติขั้นสูงมากมาย เช่น ความแม่นยํา สเกล และ MaxLength ในตอนนี้ คุณควรคํานึงถึงตัวเองด้วยชนิดที่กําหนด (TypeName) ชนิดแรกเริ่ม (Kind) และค่าคอลัมน์อาจเป็น null (IsNullable) หรือไม่

การกําหนดตาราง Schema อย่างง่าย

ตาราง Schema ของคุณจะประกอบด้วยสองคอลัมน์:

Column รายละเอียด
ชื่อ ชื่อของคอลัมน์ ซึ่งต้องตรงกับชื่อในผลลัพธ์ที่ส่งกลับโดยบริการ
ขนิด ชนิดข้อมูล M ที่คุณกําลังจะตั้งค่า ซึ่งอาจเป็นชนิดดั้งเดิม (ข้อความ ตัวเลข วันที่เวลา และอื่นๆ) หรือเป็นชนิดโดยกําเนิด (Int64.Type, Currency.Type และอื่นๆ)

ตาราง Schema แบบฮาร์ดโค้ดสําหรับAirlinesตารางจะตั้งค่าและNameคอลัมน์เป็น AirlineCodetext และมีลักษณะดังนี้:

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 มีเจ็ดเขตข้อมูล รวมถึง lists (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}
    })

คุณสามารถวางตารางเหล่านี้ทั้งหมดลงในตาราง SchemaTableSchema หลักเดียวได้ :

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

ตารางของสคีมา

ฟังก์ชันตัวช่วยเหลือ SchemaTransformTable

ฟังก์ชันผู้ช่วยเหลือที่SchemaTransformTableอธิบายไว้ด้านล่างจะถูกใช้เพื่อบังคับใช้ schema กับข้อมูลของคุณ ซึ่งจะใช้พารามิเตอร์ต่อไปนี้:

พารามิเตอร์ ชนิด คำอธิบาย
ตาราง ตาราง ตารางข้อมูลคุณจะต้องการบังคับใช้ Schema ของคุณ
สคีมา ตาราง ตาราง Schema ที่จะอ่านข้อมูลคอลัมน์จากชนิดต่อไปนี้: type table [Name = text, Type = type]
enforceSchema ตัวเลข (ไม่บังคับ) enum ที่ควบคุมลักษณะการทํางานของฟังก์ชัน
ค่าเริ่มต้น (EnforceSchema.Strict = 1) ช่วยให้แน่ใจว่าตารางผลลัพธ์จะตรงกับตาราง Schema ที่ให้ไว้โดยการเพิ่มคอลัมน์ที่ขาดหายไปและลบคอลัมน์เพิ่มเติมออก
สามารถใช้ EnforceSchema.IgnoreExtraColumns = 2 ตัวเลือก เพื่อรักษาคอลัมน์เพิ่มเติมในผลลัพธ์
เมื่อใช้ EnforceSchema.IgnoreMissingColumns = 3 ทั้งคอลัมน์ที่ขาดหายไปและคอลัมน์เพิ่มเติมจะถูกละเว้น

ตรรกะสําหรับฟังก์ชันนี้มีลักษณะดังนี้:

  1. พิจารณาว่ามีคอลัมน์ใดหายไปจากตารางต้นทางหรือไม่
  2. ตรวจสอบว่ามีคอลัมน์เพิ่มเติมใด ๆ หรือไม่
  3. ละเว้นคอลัมน์ที่มีโครงสร้าง (ของชนิด list, record, และ table) และคอลัมน์ที่ตั้งค่าเป็นชนิดany
  4. ใช้ Table.TransformColumnTypes เพื่อตั้งค่าแต่ละชนิดคอลัมน์
  5. จัดลําดับคอลัมน์ใหม่ตามลําดับที่ปรากฏในตาราง Schema
  6. ตั้งค่าชนิดบนตารางเองโดยใช้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, nulllogical, 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พารามิเตอร์จาก เป็น tabletypeและเพิ่มการเรียกไปยังTable.ChangeType อีกครั้งรายละเอียดสําหรับการทําเช่นนั้นเป็นการดําเนินการที่เฉพาะเจาะจงมากและดังนั้นจึงไม่คุ้มค่าที่จะลงในรายละเอียดที่นี่ ตัวอย่าง ตัวเชื่อมต่อ TripPin แบบขยายนี้แสดงให้เห็นถึงโซลูชันแบบ end-to-end ที่ใช้วิธีการที่ซับซ้อนมากขึ้นในการจัดการ schema