TripPin ส่วนที่ 10 - การพับคิวรีพื้นฐาน

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

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

• เรียนรู้พื้นฐานของการพับคิวรี
• เรียนรู้เกี่ยวกับฟังก์ชัน Table.View
• ทําซ้ําตัวจัดการการพับคิวรี OData สําหรับ:

• $top
• $skip
• $count
• $select
• $orderby

หนึ่งในคุณลักษณะที่มีประสิทธิภาพของภาษา M คือความสามารถในการผลักดันการแปลงการทํางานไปยังแหล่งข้อมูลพื้นฐานอย่างน้อยหนึ่งรายการ ความสามารถนี้เรียกว่า Query Folding (เครื่องมือ/เทคโนโลยีอื่น ๆ ยังอ้างอิงถึงฟังก์ชันที่คล้ายกันเช่น Predicate Pushdown หรือการมอบหมายคิวรี)

เมื่อสร้างตัวเชื่อมต่อแบบกําหนดเองที่ใช้ฟังก์ชัน M ที่มีความสามารถในการพับคิวรีที่มีอยู่ภายใน เช่น OData.Feed หรือ Odbc.DataSource ตัวเชื่อมต่อของคุณจะสืบทอดความสามารถนี้ฟรีโดยอัตโนมัติ

บทช่วยสอนนี้จําลองลักษณะการทํางานของการพับคิวรีที่มีอยู่ภายในสําหรับ OData โดยการใช้ตัวจัดการฟังก์ชันสําหรับฟังก์ชัน Table.View ส่วนนี้ของบทช่วยสอนจะใช้ตัวจัดการที่ง่ายขึ้นบางตัว (ซึ่งก็คือตัวจัดการที่ไม่จําเป็นต้องใช้การแยกวิเคราะห์นิพจน์และการติดตามรัฐ)

หากต้องการทําความเข้าใจเพิ่มเติมเกี่ยวกับความสามารถของคิวรีที่บริการ OData อาจเสนอ ให้ไปที่ อนุสัญญา OData v4 URL

หมายเหตุ

ตามที่ระบุไว้ก่อนหน้านี้ ฟังก์ชัน OData.Feed ให้ความสามารถในการพับคิวรีโดยอัตโนมัติ เนื่องจากชุด TripPin ถือว่าบริการ OData เป็น REST API ทั่วไป โดยใช้ Web.Contents แทน OData.Feed คุณจะต้องใช้ตัวจัดการ Query Folding ด้วยตัวคุณเอง สําหรับการใช้งานในโลกแห่งความจริง เราขอแนะนําให้คุณใช้ OData.Feed เมื่อใดก็ตามที่เป็นไปได้

ไปที่ ภาพรวมของการประเมินผลคิวรีและการพับคิวรีใน Power Query สําหรับข้อมูลเพิ่มเติมเกี่ยวกับ Query Folding

การใช้ Table.View

ฟังก์ชัน Table.View อนุญาตให้ตัวเชื่อมต่อแบบกําหนดเองแทนที่ตัวจัดการการแปลงเริ่มต้นสําหรับแหล่งข้อมูลของคุณ การใช้งาน Table.View จะมีฟังก์ชันสําหรับตัวจัดการที่ได้รับการสนับสนุนอย่างน้อยหนึ่งรายการ ถ้าไม่ได้ใช้ตัวจัดการ หรือส่งกลับค่า error ในระหว่างการประเมิน กลไกจัดการ M จะย้อนกลับไปยังตัวจัดการเริ่มต้น

เมื่อตัวเชื่อมต่อแบบกําหนดเองใช้ฟังก์ชันที่ไม่สนับสนุนการพับคิวรีโดยนัย เช่น Web.Contents ตัวจัดการการแปลงค่าเริ่มต้นจะดําเนินการภายในเครื่องเสมอ ถ้า REST API ที่คุณกําลังเชื่อมต่อเพื่อสนับสนุนพารามิเตอร์คิวรีเป็นส่วนหนึ่งของคิวรี Table.View ช่วยให้คุณสามารถเพิ่มการปรับให้เหมาะสมที่อนุญาตให้งานการแปลงถูกส่งไปยังบริการ

ฟังก์ชัน Table.View มีลายเซ็นดังต่อไปนี้:

Table.View(table as nullable table, handlers as record) as table

การใช้งานของคุณจะครอบคลุมฟังก์ชันแหล่งข้อมูลหลักของคุณ มีตัวจัดการที่จําเป็นสองตัวสําหรับ Table.View:

• GetType—ส่งกลับที่คาดไว้ table type ของผลลัพธ์คิวรี
• GetRows—แสดงผลลัพธ์ที่แท้จริง table ของฟังก์ชันแหล่งข้อมูลของคุณ

การใช้งานที่ง่ายที่สุดจะคล้ายกับตัวอย่างต่อไปนี้:

TripPin.SuperSimpleView = (url as text, entity as text) as table =>
    Table.View(null, [
        GetType = () => Value.Type(GetRows()),
        GetRows = () => GetEntity(url, entity)
    ]);

TripPinNavTableอัปเดตฟังก์ชันเพื่อเรียกใช้ TripPin.SuperSimpleView แทนที่จะGetEntityเป็น :

withData = Table.AddColumn(rename, "Data", each TripPin.SuperSimpleView(url, [Name]), type table),

หากคุณรันการทดสอบหน่วยซ้ํา คุณจะเห็นว่าลักษณะการทํางานของฟังก์ชันของคุณไม่เปลี่ยนแปลง ในกรณีนี้ การใช้งาน Table.View ของคุณจะส่งผ่านการเรียกไปยัง GetEntityเท่านั้น เนื่องจากคุณยังไม่ได้ใช้ตัวจัดการการแปลงใด ๆ (ยัง) พารามิเตอร์เดิม url ยังคงไม่ถูกควบคุม

การใช้งานเริ่มต้นของ Table.View

การใช้งานข้างต้นของ Table.View นั้นเรียบง่าย แต่ไม่มีประโยชน์มาก การดําเนินการต่อไปนี้ถูกใช้เป็นข้อมูลพื้นฐานของคุณ ซึ่งไม่ได้ใช้ฟังก์ชันการพับใด ๆ แต่มีการปรับนั่งร้านที่คุณต้องทํา

TripPin.View = (baseUrl as text, entity as text) as table =>
    let
        // Implementation of Table.View handlers.
        //
        // We wrap the record with Diagnostics.WrapHandlers() to get some automatic
        // tracing if a handler returns an error.
        //
        View = (state as record) => Table.View(null, Diagnostics.WrapHandlers([
            // Returns the table type returned by GetRows()
            GetType = () => CalculateSchema(state),

            // Called last - retrieves the data from the calculated URL
            GetRows = () => 
                let
                    finalSchema = CalculateSchema(state),
                    finalUrl = CalculateUrl(state),

                    result = TripPin.Feed(finalUrl, finalSchema),
                    appliedType = Table.ChangeType(result, finalSchema)
                in
                    appliedType,

            //
            // Helper functions
            //
            // Retrieves the cached schema. If this is the first call
            // to CalculateSchema, the table type is calculated based on
            // the entity name that was passed into the function.
            CalculateSchema = (state) as type =>
                if (state[Schema]? = null) then
                    GetSchemaForEntity(entity)
                else
                    state[Schema],

            // Calculates the final URL based on the current state.
            CalculateUrl = (state) as text => 
                let
                    urlWithEntity = Uri.Combine(state[Url], state[Entity])
                in
                    urlWithEntity
        ]))
    in
        View([Url = baseUrl, Entity = entity]);

ถ้าคุณดูที่การเรียกไปยัง Table.View คุณจะเห็นฟังก์ชัน wrapper พิเศษรอบhandlersๆ ระเบียนDiagnostics.WrapHandlers ฟังก์ชันตัวช่วยเหลือนี้พบได้ในโมดูลการวินิจฉัย (ซึ่งถูกนํามาใช้ใน การเพิ่มบทเรียนการวินิจฉัย ) และมอบวิธีที่มีประโยชน์ในการติดตามข้อผิดพลาดใด ๆ ที่เกิดขึ้นโดยตัวจัดการรายบุคคลโดยอัตโนมัติ

ฟังก์ชัน GetType และ GetRows ได้รับการอัปเดตเพื่อใช้ประโยชน์จากฟังก์ชันตัวช่วยเหลือใหม่สองฟังก์ชัน—CalculateSchema และCalculateUrl ในตอนนี้ การใช้งานฟังก์ชันเหล่านั้นค่อนข้างตรงไปตรงมา —โปรดสังเกตว่าฟังก์ชันดังกล่าวประกอบด้วยส่วนหนึ่งของสิ่งที่ฟังก์ชันได้ดําเนินการ GetEntity ไปแล้วก่อนหน้านี้

สุดท้าย ให้สังเกตว่าคุณกําลังกําหนดฟังก์ชันภายใน (View) ที่ยอมรับ state พารามิเตอร์ เมื่อคุณใช้ตัวจัดการเพิ่มเติม พวกเขาจะเรียกใช้ฟังก์ชันภายใน View การอัปเดต และการส่งผ่านซ้ําเมื่อ state พวกเขาไป

TripPinNavTableอัปเดตฟังก์ชันอีกครั้ง แทนที่การเรียกไปยัง TripPin.SuperSimpleView ด้วยการเรียกไปยังฟังก์ชันใหม่ TripPin.View และเรียกใช้การทดสอบหน่วยอีกครั้ง คุณยังจะไม่เห็นฟังก์ชันการทํางานใหม่ใด ๆ แต่ตอนนี้คุณมีข้อมูลพื้นฐานสําหรับการทดสอบแล้ว

การใช้ Query Folding

เนื่องจากกลไก M จะกลับไปสู่การประมวลผลภายในเครื่องโดยอัตโนมัติเมื่อไม่สามารถพับคิวรีได้ คุณต้องทําตามขั้นตอนเพิ่มเติมเพื่อตรวจสอบว่า Table.View handlers ของคุณทํางานได้อย่างถูกต้อง

วิธีแบบแมนวลในการตรวจสอบพฤติกรรมการพับคือการดู URL ที่ร้องขอการทดสอบหน่วยของคุณโดยใช้เครื่องมือเช่น Fiddler อีกวิธีหนึ่งคือ การบันทึกการวินิจฉัยที่คุณเพิ่มเพื่อ TripPin.Feed ปล่อย URL แบบเต็มที่กําลังทํางาน ซึ่ง ควร ประกอบด้วยพารามิเตอร์สตริงแบบสอบถาม OData ที่ตัวจัดการของคุณเพิ่ม

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

เราไม่สามารถพับนิพจน์ไปยังแหล่งข้อมูลได้ โปรดลองนิพจน์ที่เรียบง่ายกว่านี้

คุณสามารถทดสอบได้โดยการเพิ่ม ไฟล์ใหม่ Fact ลงในไฟล์การทดสอบหน่วยของคุณที่มีการแปลงตารางอย่างน้อยหนึ่งรายการ

// Query folding tests
Fact("Fold $top 1 on Airlines", 
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ), 
    Table.FirstN(Airlines, 1)
)

หมายเหตุ

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

ส่วนที่เหลือของบทช่วยสอนนี้แต่ละรายการเพิ่มตัวจัดการ Table.View ใหม่ คุณกําลังใช้ วิธี Test Driven Development (TDD) ซึ่งเป็นที่ที่คุณเพิ่มการทดสอบหน่วยที่ล้มเหลวก่อนจากนั้นใช้รหัส M เพื่อแก้ไขปัญหาเหล่านั้น

ส่วนตัวจัดการต่อไปนี้อธิบายฟังก์ชันการทํางานที่ให้มาโดยตัวจัดการ ไวยากรณ์คิวรีที่เทียบเท่าของ OData การทดสอบหน่วย และการใช้งาน การใช้โค้ดการยกระดับที่อธิบายไว้ก่อนหน้านี้ การใช้งานตัวจัดการแต่ละตัวจําเป็นต้องมีการเปลี่ยนแปลงสองประการ:

• การเพิ่มตัวจัดการไปยัง Table.View ที่อัปเดต state ระเบียน
• CalculateUrlการปรับเปลี่ยนเพื่อดึงค่าจาก state และ เพิ่ม ไปยังพารามิเตอร์สตริงของ url และ/หรือคิวรี

การจัดการ Table.FirstN ด้วย OnTake

ตัวจัดการOnTakeได้รับcountพารามิเตอร์ ซึ่งเป็นจํานวนแถวสูงสุดที่จะใช้จากGetRows ในคํา OData คุณสามารถแปลสิ่งนี้เป็นพารามิเตอร์คิวรี$top ได้

คุณสามารถใช้การทดสอบหน่วยต่อไปนี้:

// Query folding tests
Fact("Fold $top 1 on Airlines", 
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ), 
    Table.FirstN(Airlines, 1)
),
Fact("Fold $top 0 on Airports", 
    #table( type table [Name = text, IataCode = text, Location = record] , {} ), 
    Table.FirstN(Airports, 0)
),

การทดสอบทั้งสองอย่างนี้ใช้ Table.FirstN เพื่อกรองไปยังผลลัพธ์ที่ตั้งค่าเป็น X จํานวนแถวแรก ถ้าคุณเรียกใช้การทดสอบเหล่านี้ด้วย ข้อผิดพลาดบนการพับล้มเหลว ซึ่งตั้งค่าเป็น False (ค่าเริ่มต้น) การทดสอบควรประสบความสําเร็จ แต่ถ้าคุณเรียกใช้ Fiddler (หรือตรวจสอบบันทึกการติดตาม) โปรดสังเกตว่า คําขอที่คุณส่งไม่มีพารามิเตอร์คิวรี OData ใดๆ

ถ้าคุณตั้งค่าข้อผิดพลาดในการพับล้มเหลวTrueเป็น การทดสอบจะล้มเหลวด้วยPlease try a simpler expression.ข้อผิดพลาด เมื่อต้องการแก้ไขข้อผิดพลาดนี้ คุณจําเป็นต้องกําหนดตัวจัดการ Table.View แรกของคุณสําหรับOnTake

OnTakeตัวจัดการมีลักษณะเหมือนกับโค้ดต่อไปนี้:

OnTake = (count as number) =>
    let
        // Add a record with Top defined to our state
        newState = state & [ Top = count ]
    in
        @View(newState),

ฟังก์ชัน CalculateUrl ได้รับการอัปเดตเพื่อแยก Top ค่าจาก state เรกคอร์ด และตั้งค่าพารามิเตอร์ที่ถูกต้องในสตริงคิวรี

// Calculates the final URL based on the current state.
CalculateUrl = (state) as text => 
    let
        urlWithEntity = Uri.Combine(state[Url], state[Entity]),

        // Uri.BuildQueryString requires that all field values
        // are text literals.
        defaultQueryString = [],

        // Check for Top defined in our state
        qsWithTop =
            if (state[Top]? <> null) then
                // add a $top field to the query string record
                defaultQueryString & [ #"$top" = Number.ToText(state[Top]) ]
            else
                defaultQueryString,

        encodedQueryString = Uri.BuildQueryString(qsWithTop),
        finalUrl = urlWithEntity & "?" & encodedQueryString
    in
        finalUrl

โปรดสังเกตว่า URL ที่คุณกําลังเข้าถึงมี $top พารามิเตอร์อยู่ในขณะนี้ เนื่องจากการเข้ารหัส $top URL จะปรากฏเป็น %24topแต่บริการ OData นั้นฉลาดพอที่จะแปลงโดยอัตโนมัติ

การจัดการ Table.Skip ด้วย OnSkip

ตัวจัดการOnSkipก็เหมือนกับOnTake ซึ่ง count ได้รับพารามิเตอร์ซึ่งเป็นจํานวนแถวที่จะข้ามจากชุดผลลัพธ์ ตัวจัดการนี้แปลได้ดีกับพารามิเตอร์คิวรี OData $skip

การทดสอบหน่วย:

// OnSkip
Fact("Fold $skip 14 on Airlines",
    #table( type table [AirlineCode = text, Name = text] , {{"EK", "Emirates"}} ), 
    Table.Skip(Airlines, 14)
),
Fact("Fold $skip 0 and $top 1",
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ),
    Table.FirstN(Table.Skip(Airlines, 0), 1)
),

การนําไปใช้:

// OnSkip - handles the Table.Skip transform.
// The count value should be >= 0.
OnSkip = (count as number) =>
    let
        newState = state & [ Skip = count ]
    in
        @View(newState),

จับคู่การอัปเดตเป็น CalculateUrl:

qsWithSkip = 
    if (state[Skip]? <> null) then
        qsWithTop & [ #"$skip" = Number.ToText(state[Skip]) ]
    else
        qsWithTop,

ข้อมูลเพิ่มเติม: Table.Skip

การจัดการ Table.SelectColumns ด้วย OnSelectColumns

OnSelectColumnsตัวจัดการถูกเรียกเมื่อผู้ใช้เลือกหรือเอาคอลัมน์ออกจากชุดผลลัพธ์ ตัวจัดการได้รับ list ค่า ซึ่ง text แสดงคอลัมน์อย่างน้อยหนึ่งคอลัมน์ที่จะเลือก

ในเงื่อนไข OData การดําเนินการนี้จะแมปกับตัวเลือกคิวรี$select

ประโยชน์ของการเลือกคอลัมน์แบบพับจะปรากฏชัดเจนเมื่อคุณจัดการกับตารางที่มีคอลัมน์จํานวนมาก ตัว $select ดําเนินการ จะลบคอลัมน์ที่ไม่ได้เลือกออกจากชุดผลลัพธ์ ส่งผลให้มีการคิวรีที่มีประสิทธิภาพมากขึ้น

การทดสอบหน่วย:

// OnSelectColumns
Fact("Fold $select single column", 
    #table( type table [AirlineCode = text] , {{"AA"}} ),
    Table.FirstN(Table.SelectColumns(Airlines, {"AirlineCode"}), 1)
),
Fact("Fold $select multiple column", 
    #table( type table [UserName = text, FirstName = text, LastName = text],{{"russellwhyte", "Russell", "Whyte"}}), 
    Table.FirstN(Table.SelectColumns(People, {"UserName", "FirstName", "LastName"}), 1)
),
Fact("Fold $select with ignore column", 
    #table( type table [AirlineCode = text] , {{"AA"}} ),
    Table.FirstN(Table.SelectColumns(Airlines, {"AirlineCode", "DoesNotExist"}, MissingField.Ignore), 1)
),

การทดสอบสองครั้งแรกเลือกจํานวนคอลัมน์ที่แตกต่างกันด้วย Table.SelectColumns และรวมการโทร Table.FirstN เพื่อลดความซับซ้อนของกรณีการทดสอบ

หมายเหตุ

ถ้าการทดสอบเป็นการส่งกลับชื่อคอลัมน์เพียงอย่างเดียว (โดยใช้ Table.ColumnNames และไม่ใช่ข้อมูลใด ๆ คําขอไปยังบริการ OData จะไม่ถูกส่งจริง ๆ ทั้งนี้เนื่องจากการเรียกจะ GetType ส่งกลับ schema ซึ่งประกอบด้วยข้อมูลทั้งหมดที่กลไกจัดการ M จําเป็นต้องคํานวณผลลัพธ์

การทดสอบที่ สามใช้ตัวเลือก MissingField.Ignore ซึ่งบอกให้กลไกจัดการ M ละเว้นคอลัมน์ที่เลือกใดๆ ที่ไม่มีอยู่ในชุดผลลัพธ์ OnSelectColumnsตัวจัดการไม่จําเป็นต้องกังวลเกี่ยวกับตัวเลือกนี้ กลไก M จัดการโดยอัตโนมัติ (นั่นคือคอลัมน์ที่หายไปไม่ได้รวมอยู่ในcolumnsรายการ)

หมายเหตุ

ตัวเลือกอื่นสําหรับ Table.SelectColumns, MissingField.UseNull จําเป็นต้องมีตัวเชื่อมต่อเพื่อใช้OnAddColumnตัวจัดการ ซึ่งจะดําเนินการในบทเรียนต่อไป

การดําเนินการสําหรับ OnSelectColumns มีสองสิ่งต่อไปนี้:

• เพิ่มรายการของคอลัมน์ที่เลือกไปยังstate
• คํานวณ Schema ค่าใหม่เพื่อให้คุณสามารถตั้งค่าชนิดตารางที่ถูกต้องได้

OnSelectColumns = (columns as list) =>
    let
        // get the current schema
        currentSchema = CalculateSchema(state),
        // get the columns from the current schema (which is an M Type value)
        rowRecordType = Type.RecordFields(Type.TableRow(currentSchema)),
        existingColumns = Record.FieldNames(rowRecordType),
        // calculate the new schema
        columnsToRemove = List.Difference(existingColumns, columns),
        updatedColumns = Record.RemoveFields(rowRecordType, columnsToRemove),
        newSchema = type table (Type.ForRecord(updatedColumns, false))
    in
        @View(state & 
            [ 
                SelectColumns = columns,
                Schema = newSchema
            ]
        ),

CalculateUrl ได้รับการอัปเดตเพื่อดึงข้อมูลรายการของคอลัมน์จากสถานะ และรวมคอลัมน์เหล่านั้น (ด้วยตัวคั่น) สําหรับ $select พารามิเตอร์

// Check for explicitly selected columns
qsWithSelect =
    if (state[SelectColumns]? <> null) then
        qsWithSkip & [ #"$select" = Text.Combine(state[SelectColumns], ",") ]
    else
        qsWithSkip,

การจัดการ Table.Sort ด้วย OnSort

ตัวจัดการ OnSort ได้รับรายการของเรกคอร์ดชนิด:

type [ Name = text, Order = Int16.Type ]

แต่ละระเบียนมีNameเขตข้อมูล ที่ระบุชื่อของคอลัมน์ และOrderเขตข้อมูลที่เท่ากับ Order.Ascending หรือ Order.Descending

ในเงื่อนไข OData การดําเนินการนี้จะแมปไปยังตัวเลือกคิวรี$orderby ไวยากรณ์ $orderby มีชื่อคอลัมน์ตามด้วย asc หรือ desc เพื่อระบุลําดับจากน้อยไปหามากหรือจากมากไปหาน้อย เมื่อคุณเรียงลําดับในหลายคอลัมน์ ค่าจะถูกคั่นด้วยเครื่องหมายจุลภาค columnsถ้าพารามิเตอร์ประกอบด้วยรายการมากกว่าหนึ่งรายการ สิ่งสําคัญคือต้องรักษาลําดับที่ปรากฏ

การทดสอบหน่วย:

// OnSort
Fact("Fold $orderby single column",
    #table( type table [AirlineCode = text, Name = text], {{"TK", "Turkish Airlines"}}),
    Table.FirstN(Table.Sort(Airlines, {{"AirlineCode", Order.Descending}}), 1)
),
Fact("Fold $orderby multiple column",
    #table( type table [UserName = text], {{"javieralfred"}}),
    Table.SelectColumns(Table.FirstN(Table.Sort(People, {{"LastName", Order.Ascending}, {"UserName", Order.Descending}}), 1), {"UserName"})
)

การนําไปใช้:

// OnSort - receives a list of records containing two fields: 
//    [Name]  - the name of the column to sort on
//    [Order] - equal to Order.Ascending or Order.Descending
// If there are multiple records, the sort order must be maintained.
//
// OData allows you to sort on columns that do not appear in the result
// set, so we do not have to validate that the sorted columns are in our 
// existing schema.
OnSort = (order as list) =>
    let
        // This will convert the list of records to a list of text,
        // where each entry is "<columnName> <asc|desc>"
        sorting = List.Transform(order, (o) => 
            let
                column = o[Name],
                order = o[Order],
                orderText = if (order = Order.Ascending) then "asc" else "desc"
            in
                column & " " & orderText
        ),
        orderBy = Text.Combine(sorting, ", ")
    in
        @View(state & [ OrderBy = orderBy ]),

อัปเดตไปยัง CalculateUrl:

qsWithOrderBy = 
    if (state[OrderBy]? <> null) then
        qsWithSelect & [ #"$orderby" = state[OrderBy] ]
    else
        qsWithSelect,

การจัดการ Table.RowCount ด้วย GetRowCount

ตัวจัดการจะส่งกลับค่าเดียว ซึ่งแตกต่างจากตัวจัดการคิวรีอื่น ๆ ที่คุณกําลังใช้งาน GetRowCount ตัวจัดการจะส่งกลับค่าเดียว ซึ่งเป็นจํานวนแถวที่คาดหมายในชุดผลลัพธ์ ในคิวรี M โดยทั่วไปค่านี้จะเป็นผลลัพธ์ของการแปลง Table.RowCount

คุณมีตัวเลือกที่แตกต่างกันสองสามวิธีในการจัดการค่านี้ให้เป็นส่วนหนึ่งของคิวรี OData:

• $count พารามิเตอร์คิวรี ซึ่งส่งกลับจํานวนเป็นเขตข้อมูลแยกต่างหากในชุดผลลัพธ์
• ส่วนเส้นทาง /$count ซึ่งส่งกลับเฉพาะจํานวนรวม เป็นค่าสเกลา

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

ประโยชน์ของวิธีการของเซกเมนต์เส้นทางคือคุณได้รับเฉพาะค่าสเกลาเดียวในผลลัพธ์เท่านั้น วิธีนี้ทําให้การดําเนินการทั้งหมดมีประสิทธิภาพมากยิ่งขึ้น อย่างไรก็ตาม ตามที่อธิบายไว้ในข้อมูลจําเพาะของ OData ส่วนเส้นทาง /$count จะส่งกลับข้อผิดพลาดถ้าคุณรวมพารามิเตอร์คิวรีอื่น ๆ เช่น $top หรือ $skipซึ่งจํากัดประโยชน์

ในบทช่วยสอนนี้ คุณใช้ GetRowCount ตัวจัดการโดยใช้วิธีเซกเมนต์เส้นทาง เพื่อหลีกเลี่ยงข้อผิดพลาดที่คุณจะได้รับถ้ามีพารามิเตอร์คิวรีอื่น ๆ คุณตรวจสอบค่าสถานะอื่น ๆ และส่งกลับ "ข้อผิดพลาดที่ไม่ได้ใช้" (...) ถ้าคุณพบใด ๆ การส่งกลับข้อผิดพลาดใด ๆ จาก ตัวจัดการ Table.View จะบอกกลไกจัดการ M ว่าไม่สามารถพับการดําเนินการได้ และควรย้อนกลับไปยังตัวจัดการเริ่มต้นแทน (ซึ่งในกรณีนี้จะนับจํานวนแถวทั้งหมด)

ก่อนอื่น ให้เพิ่มการทดสอบหน่วย:

// GetRowCount
Fact("Fold $count", 15, Table.RowCount(Airlines)),

/$countเนื่องจากเซกเมนต์เส้นทางส่งกลับค่าเดียว (ในรูปแบบธรรมดา/ข้อความ) แทนที่จะเป็นชุดผลลัพธ์ JSON คุณยังต้องเพิ่มฟังก์ชันภายในใหม่ (TripPin.Scalar) สําหรับการสร้างคําขอและจัดการผลลัพธ์

// Similar to TripPin.Feed, but is expecting back a scalar value.
// This function returns the value from the service as plain text.
TripPin.Scalar = (url as text) as text =>
    let
        _url = Diagnostics.LogValue("TripPin.Scalar url", url),

        headers = DefaultRequestHeaders & [
            #"Accept" = "text/plain"
        ],

        response = Web.Contents(_url, [ Headers = headers ]),
        toText = Text.FromBinary(response)
    in
        toText;

จากนั้นการดําเนินการจะใช้ฟังก์ชันนี้ (หากไม่พบพารามิเตอร์คิวรีอื่นใน state):

GetRowCount = () as number =>
    if (Record.FieldCount(Record.RemoveFields(state, {"Url", "Entity", "Schema"}, MissingField.Ignore)) > 0) then
        ...
    else
        let
            newState = state & [ RowCountOnly = true ],
            finalUrl = CalculateUrl(newState),
            value = TripPin.Scalar(finalUrl),
            converted = Number.FromText(value)
        in
            converted,

ฟังก์ชัน CalculateUrl จะได้รับการอัปเดตเพื่อผนวก/$countเข้ากับ URL ถ้าRowCountOnlyฟิลด์ถูกตั้งค่าในstate

// Check for $count. If all we want is a row count,
// then we add /$count to the path value (following the entity name).
urlWithRowCount =
    if (state[RowCountOnly]? = true) then
        urlWithEntity & "/$count"
    else
        urlWithEntity,

ขณะนี้การทดสอบหน่วยใหม่ Table.RowCount ควรผ่าน

เมื่อต้องการทดสอบกรณีที่ใช้แสดงแทน คุณต้องเพิ่มการทดสอบอื่นที่บังคับข้อผิดพลาด

ก่อนอื่น ให้เพิ่มวิธีการผู้ช่วยเหลือที่ตรวจสอบผลลัพธ์ของ try การดําเนินการสําหรับข้อผิดพลาดการพับ

// Returns true if there is a folding error, or the original record (for logging purposes) if not.
Test.IsFoldingError = (tryResult as record) =>
    if ( tryResult[HasError]? = true and tryResult[Error][Message] = "We couldn't fold the expression to the data source. Please try a simpler expression.") then
        true
    else
        tryResult;

จากนั้นเพิ่มการทดสอบที่ใช้ทั้ง Table.RowCount และ Table.FirstN เพื่อบังคับให้เกิดข้อผิดพลาด

// test will fail if "Fail on Folding Error" is set to false
Fact("Fold $count + $top *error*", true, Test.IsFoldingError(try Table.RowCount(Table.FirstN(Airlines, 3)))),

หมายเหตุสําคัญที่นี่คือการทดสอบนี้ส่งกลับข้อผิดพลาดถ้า ข้อผิดพลาดบนพับข้อผิดพลาด ถูกตั้งค่า falseเป็น เนื่องจาก Table.RowCount การดําเนินการจะย้อนกลับไปยังตัวจัดการภายในเครื่อง (ค่าเริ่มต้น) เรียกใช้การทดสอบที่ มีข้อผิดพลาดบนการพับข้อผิดพลาด ซึ่งตั้งค่าเป็นสาเหตุTable.RowCountให้trueล้มเหลว และอนุญาตให้การทดสอบสําเร็จ

บทสรุป

Implementing Table.View สําหรับตัวเชื่อมต่อของคุณเพิ่มความซับซ้อนอย่างมากให้กับโค้ดของคุณ เนื่องจากกลไกจัดการ M สามารถประมวลผลการแปลงทั้งหมดภายในเครื่องได้ การเพิ่ม ตัวจัดการ Table.View ไม่ได้เปิดใช้งานสถานการณ์ใหม่สําหรับผู้ใช้ของคุณ แต่ส่งผลให้การประมวลผลมีประสิทธิภาพมากขึ้น (และอาจเป็นผู้ใช้ที่มีความสุข) ข้อดีประการหนึ่งหลักของ ตัวจัดการ Table.View คือเป็นทางเลือกที่ช่วยให้คุณสามารถเพิ่มฟังก์ชันการทํางานใหม่แบบเพิ่มหน่วยโดยไม่ส่งผลกระทบต่อความเข้ากันได้ย้อนหลังสําหรับตัวเชื่อมต่อของคุณ

สําหรับตัวเชื่อมต่อส่วนใหญ่ ตัวจัดการ (และพื้นฐาน) ที่สําคัญในการนําไปใช้คือ OnTake (ซึ่งแปลเป็น $top ใน OData) เนื่องจากจะจํากัดจํานวนแถวที่ส่งกลับ ประสบการณ์การใช้งาน Power Query จะดําเนินการแถว1000ต่างๆ OnTake เสมอเมื่อแสดงตัวอย่างในตัวนําทางและตัวแก้ไขคิวรี ดังนั้นผู้ใช้ของคุณอาจเห็นการปรับปรุงประสิทธิภาพการทํางานที่สําคัญเมื่อทํางานกับชุดข้อมูลขนาดใหญ่