TripPin ส่วนที่ 6 - เค้าร่าง

บทช่วยสอนแบบหลายส่วนนี้ครอบคลุมถึงการสร้างส่วนขยายแหล่งข้อมูลใหม่Power Queryแหล่งข้อมูล บทช่วยสอนมีไว้เพื่อจะจัดการแต่ละบทเรียนตามลวตาของตัวเชื่อมต่อที่สร้างขึ้นในบทเรียนก่อนหน้า — โดยเพิ่มความสามารถใหม่ให้กับตัวเชื่อมต่อของคุณแบบเพิ่มหน่วย

ในบทเรียนนี้ คุณจะต้อง:

  • กําหนดเค้าร่างแบบคงที่REST API
  • ตั้งค่าชนิดข้อมูลแบบไดนามิกของคอลัมน์
  • บังคับใช้โครงสร้างตารางเพื่อหลีกเลี่ยงข้อผิดพลาดการแปลงเนื่องจากคอลัมน์ที่ขาดหายไป
  • ซ่อนคอลัมน์จากชุดผลลัพธ์

หนึ่งในข้อดีที่ยิ่งใหญ่ของบริการ OData ผ่านมาตรฐานREST APIเป็นข้อ$metadataนิยาม เอกสาร$metadataอธิบายข้อมูลที่พบบนบริการนี้ รวมถึง Schema กับเอนทิตี (ตาราง) และเขตข้อมูล (คอลัมน์) ทั้งหมด ฟังก์ชันจะใช้นิยาม schema นี้เพื่อตั้งค่าข้อมูลชนิดข้อมูลโดยอัตโนมัติ ดังนั้นแทนที่จะรับข้อความและเขตข้อมูลตัวเลขทั้งหมด (เช่นที่คุณต้องการจาก) ผู้ใช้ปลายทางจะได้รับวันที่ จํานวนเต็ม เวลา และอื่นๆ ซึ่งจะมอบประสบการณ์การใช้งานโดยรวม OData.FeedJson.Document ที่ดียิ่งขึ้น

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

หมายเหตุ

วิธีการที่อธิบายไว้ที่นี่ควรไปใช้งานกับบริการ REST มากมาย บทเรียนในอนาคต จะสร้างขึ้นตามวิธีนี้โดยการบังคับใช้ Schema บนคอลัมน์ที่มีโครงสร้าง (ระเบียน รายการ ตาราง) และจัดเตรียมการใช้งานตัวอย่างที่สามารถสร้างตาราง Schema จากเอกสาร CSDL หรือ JSON Schema ได้ทางโปรแกรม

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

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

การดู Schema ที่มีอยู่กับ Table.Schema

ตัวเชื่อมต่อที่สร้างขึ้นในบทเรียนก่อนหน้านี้แสดงสามตารางจากบริการ TripPin , — Airlines Airports People และ เรียกใช้คิวรีต่อไปนี้เพื่อดู Airlines ตาราง:

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    data

ในผลลัพธ์ คุณจะเห็นสี่คอลัมน์ที่ส่งคืน:

  • @odata.id
  • @odata.editLink
  • รหัสเที่ยวบิน
  • ชื่อ

แผนผังไม่มี schema

คอลัมน์ @odata " .*" เป็นส่วนหนึ่งของโพรโทคอล OData และไม่ใช่บางสิ่งที่คุณต้องหรือต้องการแสดงให้ผู้ใช้ปลายทางของตัวเชื่อมต่อของคุณ AirlineCode``Nameและ มีสองคอลัมน์ที่คุณต้องการเก็บไว้ หากคุณดูสคีมาของตาราง (โดยใช้ฟังก์ชัน Table.Schema ที่มีประโยชน์) คุณจะเห็นว่าคอลัมน์ทั้งหมดในตารางมีชนิดข้อมูล Any.Type เป็น

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    Table.Schema(data)

แผนผังตาราง.Schema

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

การนิยามตาราง Schema อย่างง่าย

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

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

ตาราง hardcoded schema ของ 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 เขตข้อมูลเจ็ดเขต รวมถึงรายการ ( 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}
    })

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

ฟังก์ชัน SchemaTransformTable ผู้ช่วยเหลือที่อธิบายไว้ด้านล่างจะถูกใช้เพื่อบังคับใช้ 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 และคอลัมน์ที่ type any ตั้งค่าเป็น
  4. ใช้ Table.TransformColumnTypes เพื่อตั้งค่าแต่ละชนิดคอลัมน์
  5. จัดล 3 คอลัมน์ตามลมักจะปรากฏในตาราง schema
  6. ตั้งค่าชนิดบนตารางเองโดยใช้Value.ReplaceType

หมายเหตุ

ขั้นตอนสุดท้ายในการตั้งค่าชนิดตารางจะขจัดความต้องการใช้ UI Power Query เพื่ออนุมานข้อมูลเมื่อดูผลลัพธ์ในตัวแก้ไขคิวรี ซึ่งลบปัญหาการร้องขอสองครั้งที่คุณเห็นที่ ส่วนท้ายของบทช่วยสอนก่อนหน้านี้

สามารถคัดลอกและวางรหัสผู้ช่วยเหลือต่อไปนี้ลงในส่วนขยายของคุณ:

EnforceSchema.Strict = 1;               // Add any missing columns, remove extra columns, set table type
EnforceSchema.IgnoreExtraColumns = 2;   // Add missing columns, do not remove extra columns
EnforceSchema.IgnoreMissingColumns = 3; // Do not add or remove columns

SchemaTransformTable = (table as table, schema as table, optional enforceSchema as number) as table =>
    let
        // Default to EnforceSchema.Strict
        _enforceSchema = if (enforceSchema <> null) then enforceSchema else EnforceSchema.Strict,

        // Applies type transforms to a given table
        EnforceTypes = (table as table, schema as table) as table =>
            let
                map = (t) => if Type.Is(t, type list) or Type.Is(t, type record) or t = type any then null else t,
                mapped = Table.TransformColumns(schema, {"Type", map}),
                omitted = Table.SelectRows(mapped, each [Type] <> null),
                existingColumns = Table.ColumnNames(table),
                removeMissing = Table.SelectRows(omitted, each List.Contains(existingColumns, [Name])),
                primativeTransforms = Table.ToRows(removeMissing),
                changedPrimatives = Table.TransformColumnTypes(table, primativeTransforms)
            in
                changedPrimatives,

        // Returns the table type for a given schema
        SchemaToTableType = (schema as table) as type =>
            let
                toList = List.Transform(schema[Type], (t) => [Type=t, Optional=false]),
                toRecord = Record.FromList(toList, schema[Name]),
                toType = Type.ForRecord(toRecord, false)
            in
                type table (toType),

        // Determine if we have extra/missing columns.
        // The enforceSchema parameter determines what we do about them.
        schemaNames = schema[Name],
        foundNames = Table.ColumnNames(table),
        addNames = List.RemoveItems(schemaNames, foundNames),
        extraNames = List.RemoveItems(foundNames, schemaNames),
        tmp = Text.NewGuid(),
        added = Table.AddColumn(table, tmp, each []),
        expanded = Table.ExpandRecordColumn(added, tmp, addNames),
        result = if List.IsEmpty(addNames) then table else expanded,
        fullList =
            if (_enforceSchema = EnforceSchema.Strict) then
                schemaNames
            else if (_enforceSchema = EnforceSchema.IgnoreMissingColumns) then
                foundNames
            else
                schemaNames & extraNames,

        // Select the final list of columns.
        // These will be ordered according to the schema table.
        reordered = Table.SelectColumns(result, fullList, MissingField.Ignore),
        enforcedTypes = EnforceTypes(reordered, schema),
        withType = if (_enforceSchema = EnforceSchema.Strict) then Value.ReplaceType(enforcedTypes, SchemaToTableType(schema)) else enforcedTypes
    in
        withType;

ปรับปรุงตัวเชื่อมต่อ TripPin

ตอนนี้คุณจะแก้ไขการเปลี่ยนแปลงต่อไปนี้ไปยังตัวเชื่อมต่อของคุณเพื่อใช้รหัสบังคับใช้ Schema ใหม่

  1. กําหนดตารางสคีมาหลัก ( SchemaTable ) ที่มีข้อกําหนด schema ของคุณทั้งหมด
  2. อัปเดต TripPin.Feed GetPage , และ GetAllPagesByNextLink เพื่อ schema ยอมรับพารามิเตอร์
  3. บังคับใช้สคีมา GetPage ของคุณใน
  4. อัปเดตโค้ดตารางการนําทางของคุณเพื่อรวมแต่ละตารางด้วยการเรียกฟังก์ชันใหม่ ( ) ซึ่งจะ GetEntity — ให้ความยืดหยุ่นในการจัดการนิยามตารางในอนาคต

ตาราง Schema หลัก

ตอนนี้คุณจะรวมนิยามสคีมาของคุณลงในตารางเดียว และเพิ่มฟังก์ชันผู้ช่วยเหลือ ( ) ที่ GetSchemaForEntity ช่วยให้คุณสามารถค้นหานิยามตามชื่อเอนทิตี้ (ตัวอย่างเช่น GetSchemaForEntity("Airlines") )

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

    {"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}
    })}
});

GetSchemaForEntity = (entity as text) as table => try SchemaTable{[Entity=entity]}[SchemaTable] otherwise error "Couldn't find entity: '" & entity &"'";

การเพิ่มการสนับสนุนเค้าร่างไปยังฟังก์ชันข้อมูล

ในตอนนี้ คุณจะเพิ่มพารามิเตอร์ schema ที่เลือกได้ TripPin.Feed ไปยังฟังก์ชัน GetPage , GetAllPagesByNextLink และ ซึ่งจะช่วยให้คุณสามารถส่งผ่านสคีมา (เมื่อคุณต้องการ) ไปยังฟังก์ชันการแบ่งหน้าซึ่งจะถูกปรับใช้กับผลลัพธ์คุณจะได้รับกลับมาจากบริการ

TripPin.Feed = (url as text, optional schema as table) as table => ...
GetPage = (url as text, optional schema as table) as table => ...
GetAllPagesByNextLink = (url as text, optional schema as table) as table => ...

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

บังคับใช้สคีมา

การบังคับใช้เค้าร่างจริงจะเสร็จสิ้นใน GetPage ฟังก์ชันของคุณ

GetPage = (url as text, optional schema as table) as table =>
    let
        response = Web.Contents(url, [ Headers = DefaultRequestHeaders ]),        
        body = Json.Document(response),
        nextLink = GetNextLink(body),
        data = Table.FromRecords(body[value]),
        // enforce the schema
        withSchema = if (schema <> null) then SchemaTransformTable(data, schema) else data
    in
        withSchema meta [NextLink = nextLink];

[หมายเหตุ] GetPage การดําเนินการ นี้ใช้ Table.FromRecords เพื่อแปลงรายการของระเบียนในการตอบสนอง JSON เป็นตาราง ข้อเสียหลักในการใช้ Table.FromRecords ก็คือสมมติว่าระเบียนทั้งหมดในรายการมีชุดเขตข้อมูลเดียวกัน ซึ่งใช้งานได้กับบริการ TripPin เนื่องจากบันทึก OData ถูกขายให้ประกอบด้วยเขตข้อมูลเดียวกัน แต่อาจไม่เป็นกรณีหรับ REST API ทั้งหมด การใช้งานที่มีประสิทธิภาพมากขึ้นจะใช้การรวมกันของTable.FromList และ Table.ExpandRecordColumn บทช่วยสอนในภายหลังจะเปลี่ยนแปลงการดําเนินการเพื่อรับรายการคอลัมน์จากตาราง schema เพื่อให้มั่นใจว่าไม่มีคอลัมน์สูญหายหรือหายไปในระหว่างการแปล JSON เป็น M

การเพิ่มฟังก์ชัน GetEntity

ฟังก์ชัน GetEntity จะตัดการเรียกของคุณไปยัง TripPin.Feed ซึ่งจะค้นหานิยาม schema ตามชื่อเอนทิตี และสร้าง URL แบบเต็ม

GetEntity = (url as text, entity as text) as table => 
    let
        fullUrl = Uri.Combine(url, entity),
        schemaTable = GetSchemaForEntity(entity),
        result = TripPin.Feed(fullUrl, schemaTable)
    in
        result;

จากนั้นคุณจะอัปเดต TripPinNavTable ฟังก์ชันของคุณให้เป็นการเรียก GetEntity แทนที่จะเรียกแบบอินไลน์ทั้งหมด ประโยชน์หลักคือช่วยให้คุณปรับเปลี่ยนโค้ดการสร้างเอนทิตีของคุณต่อไปได้ โดยไม่เพียงแค่แตะตรรกะตารางการ nav ของคุณ

TripPinNavTable = (url as text) as table =>
    let
        entitiesAsTable = Table.FromList(RootEntities, Splitter.SplitByNothing()),
        rename = Table.RenameColumns(entitiesAsTable, {{"Column1", "Name"}}),
        // Add Data as a calculated column
        withData = Table.AddColumn(rename, "Data", each GetEntity(url, [Name]), type table),
        // Add ItemKind and ItemName as fixed text values
        withItemKind = Table.AddColumn(withData, "ItemKind", each "Table", type text),
        withItemName = Table.AddColumn(withItemKind, "ItemName", each "Table", type text),
        // Indicate that the node should not be expandable
        withIsLeaf = Table.AddColumn(withItemName, "IsLeaf", each true, type logical),
        // Generate the nav table
        navTable = Table.ToNavigationTable(withIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
    in
        navTable;

ทุกอย่างรวมเข้าด้วยกัน

เมื่อมีการเปลี่ยนแปลงโค้ดทั้งหมดแล้ว ให้รวบรวมและเรียกใช้คิวรีทดสอบที่เรียกใช้ Table.Schema ตาราง Qrdตามการโทรออกอีกครั้ง

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    Table.Schema(data)

ตอนนี้คุณเห็นว่าตารางรายชื่อเที่ยวบินของคุณมีสองคอลัมน์ที่คุณกําหนดไว้ใน schema เท่านั้น:

แผนผังกับ Schema

ถ้าคุณเรียกใช้โค้ดเดียวกันกับตารางบุคคล...

let
    source = TripPin.Contents(),
    data = source{[Name="People"]}[Data]
in
    Table.Schema(data)

คุณจะเห็นว่าชนิดที่กถูกใช้ Int64.Type () ถูกตั้งค่าอย่างถูกต้อง

บุคคลที่มี Schema

สิ่งสําคัญที่ต้องทราบคือ การ SchemaTransformTable ดําเนินการนี้จะไม่ปรับเปลี่ยนชนิดของคอลัมน์ และ list record แต่ Emails AddressInfo จะยังคงพิมพ์คอลัมน์ และ list เป็น เนื่องจากจะ Json.Document แมปอาร์เรย์ JSON ไปยังรายการ M และออบเจ็กต์ JSON กับเรกคอร์ด M อย่างถูกต้อง หากคุณต้องการขยายคอลัมน์เรกคอร์ดหรือรายการPower Query คุณจะเห็นว่าคอลัมน์ที่ขยายทั้งหมดจะเป็นชนิดใด ๆ บทช่วยสอนในอนาคตจะช่วยปรับปรุงการดําเนินการเพื่อตั้งค่าข้อมูลประเภทซ้555ให้กับประเภทที่ซับซ้อนแบบซ้อนกัน

บทสรุป

บทช่วยสอนนี้ให้ตัวอย่างการดําเนินการเพื่อบังคับใช้ Schema บนข้อมูล JSON ที่ส่งกลับจากบริการ REST ในขณะที่ตัวอย่างนี้ใช้รูปแบบตาราง schema แบบฮาร์ดโค้ดอย่างง่าย แต่วิธีการสามารถขยายตามได้โดยการสร้างนิยามตาราง Schema แบบไดนามิกจากแหล่งข้อมูลอื่น เช่น ไฟล์ JSON schema หรือบริการเมตาดาต้า/จุดสิ้นสุดที่เปิดเผยโดยแหล่งข้อมูล

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

หากคุณดูตาราง บุคคล ที่ใช้ตัวเชื่อมต่อ TripPinจากบทเรียนก่อนหน้า คุณจะเห็นว่าคอลัมน์ทั้งหมดมีไอคอน 'พิมพ์ใด ๆ' (แม้แต่คอลัมน์ที่ประกอบด้วยรายการ):

บุคคลที่ไม่มี Schema

การเรียกใช้คิวรีเดียวกันด้วยตัวเชื่อมต่อ TripPin จากบทเรียนนี้ ตอนนี้คุณจะเห็นว่าข้อมูลชนิดแสดงผลอย่างถูกต้องแล้ว

บุคคลที่มี Schema

ขั้นตอนถัดไป

TripPin ส่วนที่ 7 - เค้าร่างขั้นสูงพร้อมชนิด M