เก่งโค้ดงาน Business Intelligence ตอนที่ 6

เก่งโค้ดงาน Business Intelligence ตอนที่ 6

พารามิเตอร์ตัวที่สี่

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

หากเรากำหนดให้พารามิเตอร์ตัวที่สี่ของฟังก์ชัน Table.Group() เป็นแบบ GroupKind.Local ซึ่งมีความหมายว่า เมื่อเราจัดกลุ่มคอลัมน์ ลำดับการเรียงข้อมูลจะมีผล โดยจะจัดกลุ่มเพียงเฉพาะย่านที่มีค่าต่อเนื่องกันเท่านั้น รูปที่ 1 เป็นตัวอย่างโค้ดแสดงการใช้ Table.Group() เพื่อหาจำนวนแถวข้อมูลที่จัดกลุ่มโดยคอลัมน์ WeekdayOrWeekend บรรทัดที่ 10 เราใส่พารามิเตอร์ตัวที่สี่เป็น GroupKind.Global ที่แม้ไม่ใส่ก็ได้ผลอย่างเดียวกัน เพราะเป็นค่าปริยาย แต่ก็ใส่ไว้เพื่อความชัดเจนได้เหมือนกัน
 นี่คือตารางแสดงยอดขายเรียงตามวันที่ มีคอลัมน์ WeekdayOrWeekend เก็บค่าที่บอกว่าวันไหนเป็นวันทำงาน วันไหนเป็นวันหยุด

เมื่อรันนิจน์จะได้ผลลัพธ์เป็นตารางที่มีสองคอลัมน์ สองแถว มีค่า 8 และ 4 ตามลำดับดังนี้

ในกรณีที่เราไม่เอา GroupKind.Global แต่จะเอาแบบ GroupKind.Local โค้ดจะเป็นอย่างบรรทัดที่ 13-21 จะเห็นว่าโค้ดเหมือนเดิมทุกอย่าง ยกเว้นพารามิเตอร์ตัวที่สี่ ที่เปลี่ยนจาก GroupKind.Global ไปเป็น GroupKind.Local (ดูบรรทัด 28) ผลลัพธ์การทำงานจะเป็นอย่างที่เห็นในตารางข้างล่าง

สาเหตุที่ตารางนี้มีสี่แถวแทนที่จะเป็นสองแถวเหมือนอย่างตารางก่อนหน้านี่ เนื่องจากการทำงานของฟังก์ชัน Table.Group() จะทำให้ค่ารวมลำดับแถวที่มีค่าเดียวกันในคอลัมน์ WeekdayOrWeekend ซึ่งแบ่งออกได้สี่กลุ่ม คือ กลุ่มแรก สามแถวแรกของตารางต้นทางเป็นวันทำงาน กลุ่มที่สอง สองแถวถัดไปเป็นวันหยุด กลุ่มที่สาม ห้าแถวถัดไปเป็นวันทำงาน และกลุ่มสุดท้าย คือสองแถวสุดท้ายที่เป็นวันหยุด

การเรียงข้อมูลในตาราง

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

จากข้อมูลใน “ตารางแสดงยอดขายเรียงตามวันที่” อันเดิม เราสามารถใช้นิพจน์เรียงข้อมูลอย่างที่เห็นในบรรทัดที่ 23-29 โดยเราจะเรียงคอลัมน์ WeekdayOrWeekend ตามลำดับจากมากไปน้อย (Descending) และจะเรียงคอลัมน์ Sales ตามลำดับน้อยไปมาก (Ascending) โปรดสังเกตพารามิเตอร์ตัวที่สอง ที่เป็น “ลิสต์ของลิสต์” ใช้เพื่อส่งค่าร่วมกันของคอลัมน์และวิธีเรียงข้อมูล ผลลัพธ์ของการทำงานเป็นดังนี้

การคัดกรองข้อมูลในตาราง

ฟังก์ชันซึ่งทำหน้าที่คัดกรองข้อมูลในตาราง คล้ายฟังก์ชันเพื่อคัดกรองข้อมูลในลิสต์ ฟังก์ชัน Table.First() และฟังก์ชัน Table.Last() ให้ค่าแถวแรกและแถวสุดท้ายของตาราง ขณะที่ฟังก์ชัน Table.SelectRows() ทำงานคล้ายฟังก์ชัน List.Select() แม้ว่าเราจะสามารถอ้างถึงค่าในคอลัมน์โดยใช้พารามิเตอร์ตัวที่สองได้โดยไม่ต้องใช้คำสั่ง _notation

ยกตัวอย่างเช่น จากข้อมูลใน “ตารางแสดงยอดขายเรียงตามวันที่” อันเดิม เราสามารถใช้นิพจน์ในภาพที่ 2 บรรทัด 3-7 เพื่อเอาแถวข้อมูลทั้งหมดที่มีค่าในคอลัมน์ Sales น้อยกว่า 6 ออกโดยในกรณีนี้ เราจะใช้ [Sales] ทำหน้าที่อ้างถึงค่าของแถวในคอลัมน์ Sales แล้วให้ฟังก์ชันวนค่าตั้งแต่แถวแรกไปจนครบ ผลลัพธ์การทำงานเป็นดังนี้

การทำ Pivot และ Unpivot ตาราง

เราสามารถ unpivot ตารางได้โดยใช้ฟังก์ชัน Table.UnPivot() ซึ่งทำหน้าที่ unpivot ตามรายการคอลัมน์ที่กำหนด และมีฟังก์ชัน Table.UnpivotOtherColumns() ซึ่งจะ unpivot ทุกคอลัมน์ ยกเว้นคอลัมน์ที่ระบุ ซิกเนเจอร์ของฟังก์ชัน Table.Unpivot เป็นอย่างที่เห็นในรูปที่ 2 บรรทัดที่ 9-15 ส่วนซิกเนเจอร์ของฟังก์ชัน Table.UnpivotOtherColumns เป็นอย่างที่เห็นในรูปที่ 2 บรรทัดที่ 17-23

หากเราต้องการทำ UnpivotOtherColumns กับ “ตารางแสดงยอดขายเรียงตามวันที่” อันเดิมโดยใช้นิพจน์ในบรรทัด 25-30 จะได้ผลลัพธ์ดังนี้

สิ่งที่เกิดขึ้นคือแทนที่จะมีคอลัมน์ Sales และ Units ตารางใหม่กลายเป็นมีคอลัมน์ Attribute ซึ่งมีข้อมูลภายในเป็นข้อมูลจาก คอลัมน์ Sales และ Units เรียงสลับกันจนครบทุกแถว

การทำ Pivot โดยทั่วไปแล้วสามารถทำได้โดยใช้ตัวเลือกคำสั่งต่าง ๆ ในโปรแกรม Excel แต่การการทำ Pivot บางอย่างทำได้ด้วยการเขียนโค้ดเท่านั้น ยกตัวอย่างเช่น การทำที่ตรงกันข้ามกับตัวอย่างที่ผ่านมาคือ Pivot ให้แถวกลายเป็นคอลัมน์ สามารถทำได้โดยใช้ฟังก์ชัน Table.Pivot() ที่มีซิกเนอร์เจอร์อย่างที่เห็นในรูปที่ 3 บรรทัด 3-10

โดยใช้ “ตารางแสดงยอดขายเรียงตามวันที่” อันเดิม เราสามารถ Pivot ตารางนี้ให้คอลัมน์ Sales ที่เดิมมีอยู่อันเดียว แยกเป็นสองคอลัมน์ โดยใช้นิพจน์ในบรรทัด 12-19 จะได้ผลลัพธ์ดังนี้

โปรดสังเกตว่าคอลัมน์ Units ไม่ได้รับผลกระทบจากการทำ Pivot และเราจำเป็นต้องนำค่ารายการในลิสต์ของคอลัมน์ WeekdayOrWeekend มาป้อนให้แทนที่จะเขียนเป็นฮาร์ดโค้ด หรือจะเขียนโดยใช้ร่วมกับฟังก์ชัน Table.Column และฟังก์ชัน List.Distinct อย่างที่เห็นในบรรทัดที่ 21-28 ก็ได้เช่นกัน

พารามิเตอร์ตัวสุดท้ายของฟังก์ชัน Table.Pivot() ที่เห็นในตัวอย่างนี้คือ List.Sum (ดูบรรทัดที่ 18,27) จะทำงานเมื่อการ Pivot ต้องใช้การหาผลรวมใด ๆ แต่ในตัวอย่างนี้ไม่มีการหาผลรวมจึงไม่จำเป็น และจะไม่ใส่ก็ได้ แต่ในกรณีที่คอลัมน์ WeekdayOrWeekend กับคอลัมน์ Date มีข้อมูลหลายแถวผสมกัน จำเป็นต้องรวมค่าของแถว เพราะตารางผลลัพธ์มีข้อมูลแค่แถวเดียวต่อหนึ่งวัน
บทความตอนนี้พูดถึงการประยุกต์ใช้งานคิวรีภาษาเอ็มกับตารางในเวิร์กชีทของเอ็กซ์เซล เรื่องการจัดกลุ่มข้อมูลในตาราง ใช้งานฟังก์ชัน Table.Group() ต่อจากตอนที่แล้ว และมีเรื่องการเรียงข้อมูลโดยใช้ ฟังก์ชัน Table.Sort() และฟังก์ชัน Table.ReverseRows()  ที่เรียงข้อมูลกลับหลัง การทำ Pivot และ Unpivot ในตอนต่อไปจะเป็นเรื่องการประยุกต์ใช้คิวรีภาษาเอ็มเพื่อการประมวลผลที่มีประโยชน์ต่องานบิซิเนสอินเทลลิเจนซ์ในระดับเข้มข้นขึ้น