เก่งโค้ดงาน Business Intelligence

บทความนี้เกี่ยวกับเทคนิคการเขียนโค้ดด้วยภาษา Microsoft Power Query Formula Language (ต่อไปจะเรียกย่อว่าภาษา M) ภาษา DAX (Data Analysis Expressions) และภาษา R
ซึ่งเป็นสามภาษาหลักที่ได้รับการสนับสนุนจากไมโครซอฟท์เพื่อใช้กับงาน “บิซิเนสอินเทลลิเจนซ์” (Business Intelligence) ในโปรแกรม Microsoft Power BI และ Excel และ Microsoft SQL Server

บทความนี้ยาวประมาณ 12 ตอน ภาพประกอบส่วนมากจะเป็นตัวอย่างโค้ดแสดงวิธีทำ BI ในภาษา M , R และ DAXุ

ถ้าต้องการป้อนพิมพ์โค้ดภาษา M เพื่อทดสอบการทำงานท่านจะต้องมีโปรแกรม Microsoft Power BI Desktop หรือโปรแกรม Microsoft Excel เวอร์ชัน 2016 หรือใหม่กว่า
ถ้าต้องการป้อนพิมพ์โค้ดภาษา R ใน Power BI Desktop หรือใน Microsoft SQL Server ท่านจะต้องติดตั้งตัวแปรภาษา R ที่ดาวน์โหลดได้ฟรีจาก CRAN และควรมีเครื่องอำนวยความสะดวกเพื่อทดสอบและรันสคริปต์เช่นโปรแกรม Microsoft Visual Studio 2017 community edition หรือใหม่กว่า หรือโปรแกรม R Studio

นักพัฒนาแอพสำหรับ Excel มักคุ้นเคยกับภาษา VBA และภาษา C# อยู่แล้วไม่มากก็น้อย สองภาษานี้เป็นภาษาแบบ “เน้นการสั่งให้ทำกระบวนการที่ต้องการ” (imperative)
ขณะที่ภาษา M และ R เป็นภาษาแบบ “เน้นฟังก์ชัน” (functional) ซึ่งมีวิธีคิดแตกต่างจากภาษา VBA และภาษา C# ไปมาก
เมื่อเริ่มต้นเรียนอาจรู้สึกว่ายาก แต่เมื่อเข้าใจแล้วจะเห็นประโยชน์ของมันและพบว่าคุ้มกับเวลาที่เสียไปในการเรียนรู้

การเรียนรู้วิธีเขียนภาษา M และ R ใน Power BI และ Excel จะช่วยให้ท่านวิเคราะห์ข้อมูล (Data Analysis) ได้ยืดหยุ่นและคล่องตัวกว่าการใช้ DAX (Data Analysis Expressions) แต่เพียงอย่างเดียว เพราะมันจะช่วยให้ท่านจัดการกับข้อมูลได้เป็นระบบอย่างช่ำชอง และยังสามารถนำไปประยุกต์ใช้กับการทำวิทยาการข้อมูล (Data science) เช่น

การแปลง (Data Transformation)
การชำระ (Data cleansing)
การทำฟีเจอร์ (Feature Engineering) และ
การสร้างภาพ (Data visualization) ได้อีกด้วย

Microsoft Power Query Formula Language

ภาษา M (M Lanugage) เป็นภาษาที่ถูกสร้างโดยบริษัทไมโครซอฟท์เพื่อใช้เป็นภาษาสคริปต์ที่ทำงานในหลังฉากเมื่อเราสร้างสูตรด้วย Power Query ใน Power BI และ Excel
หน้าที่หลักของภาษา M คือการแปลงลักษณะข้อมูล (เช่นการสร้างตาราง การแปลงข้อมูลในตาราง การทำ pivot หรือ unpivot ฯลฯ)
โดยจะทำอย่างเป็นขบวนการทีละขั้นตอน กล่าวคือ

ขั้นตอนปัจจุบันจะทำงานกับข้อมูลที่ถูกเปลี่ยนแปลงจากขั้นตอนก่อนหน้า และ
ขั้นตอนต่อไปจะแปลงข้อมูลให้แตกต่างไปจากกระบวนการปัจจุบันมากยิ่งขึ้นไปอีก

การเขียนสคริปต์ภาษา M จะยืดหยุ่นกว่าการใช้ Power Query ซึ่งเป็นส่วนติดต่อกับผู้ใช้ทางภาพ (Graphical User Interface)
เพราะสามารถป้อนพิมพ์โค้ดได้อย่างพลิกแพลงหลากหลายกว่าโค้ดที่ได้จากการเลือกเมนูคำสั่งและตัวเลือกต่าง ๆ

ในโปรแกรม Excel 2016 ท่านสามารถป้อนคำสั่งภาษา M ใน Query Editor หรือที่แถบสำหรับป้อนสูตรก็ได้
ส่วนในโปรแกรม Power BI Desktop ป้อนได้ที่ Advance Query Editor จากนั้นสามารถสั่งให้ทำงานที่ละขั้น เมื่อทำครบเสร็จแล้ว ท่านอาจจะทดลองยกเลิกขั้นตอนย้อนหลังไปทีละขั้นเพื่อตรวจสอบการทำงานของโค้ดก็ได้

กรุณาอย่าสับสนระหว่างภาษา M กับภาษา DAX เพราะเป็นคนละภาษาที่ไม่มีอะไรเกี่ยวข้องกัน

ภาษา M มีลักษณะเป็นภาษาคอมพิวเตอร์เต็มรูปแบบ ลักษณะคล้ายภาษา F# เช่น

เป็นภาษาเชิงฟังก์ชัน (functional programming language)
คำสั่งเป็นตัวอักษรเล็กทั้งหมด
การประกาศตัวแปรสามารถทำได้โดยไม่ต้องกำหนดชนิดข้อมูล ตัวแปรมีลักษณะพลวัตคือเปลี่ยนไทป์ไปมาได้ มีไทป์พื้นฐานคือตัวเลข ตัวอักษร บูลีนและ null มีไทป์แบบเก็บข้อมูลเป็นชุดหลายแบบ เช่น list, record และ table
ลักษณะของฟังก์ชันจะเหมือนฟังก์ชันทางคณิตศาสตร์ ไม่ใช่ฟังก์ชันที่เป็นโปรแกรมย่อยอย่างในภาษาซี

ข้อควรระวังคือภาษา M มีภาวะอ่อนไหวต่อตัวอักษรเล็กใหญ่ (Case Sensitive) ตัวอักษร a ไม่เหมือน A

ภาษา DAX

ภาษาหรือนิพจน์ DAX (Data Analysis Expressions) คือ ชุดของฟังก์ชัน ตัวกระทำ และตัวคงค่า ที่เราสามารถนำมาใช้ในสูตร หรือในนิพจน์ เพื่อคำนวณให้ได้มาซึ่งค่าหนึ่งค่าหรือมากกว่า

การใช้ DAX ช่วยให้เราสามารถสังเคราะห์สารสนเทศใหม่ได้จากข้อมูลที่มีอยู่เดิมในแบบจำลอง
ท่านสามารถใช้ DAX เพื่อวิเคราะห์ข้อมูลที่มีโจทย์ซับซ้อนเกินกว่าที่จะทำได้โดยใช้เพียงเครื่องมือพื้น ๆ อย่าง PivotTable หรือ PivotCharts
ท่านสามารถเขียนนิพจน์ DAX ได้ในช่องป้อนสูตรของทั้งโปรแกรม Power BI และ Excel

ภาษา R

ภาษา R เป็นภาษาที่ใช้กันอย่างแพร่หลายในงานจัดการข้อมูล , การคำนวณทางสถิติและกราฟฟิก

ไมโครซอฟท์สนับสนุนการใช้ภาษา R ใน SQL Server, Power BI และ Machine Learning

ยกตัวอย่างเช่นในโปรแกรม Power BI Desktop
เราสามารถใช้คำสั่งภาษา R สร้างวิสชวลใส่ในรายงานหรือเดชบอร์ดได้โดยตรง

ส่วนใน Azure Machine Learning Studio
เราสามารถป้อนพิมพ์สคริปต์ภาษา R เพื่อจัดการกับดาต้าเฟรมได้อย่างซับซ้อน เช่น การทำฟีเจอร์เอนจิเนียริง
การใช้ R เพื่อทำอัลกอริทึมของแมชชีนเลินนิง

ภาษา R เป็นภาษาที่มีลักษณะเป็น functional programming เช่นเดียวกับภาษา M

การป้อนพิมพ์ภาษา R ใน Excel ทำได้หลังจากติดตั้ง addin ของ Exel ชื่อ RExecl

คุณสมบัติที่น่าสนใจของภาษา R มีหลายอย่าง อาทิ

สนับสนุนการกระทำต่อข้อมูลในลักษณะต่าง ๆ
มีเพคเจ็กเกี่ยวกับการบริหารจัดการข้อมูลจำนวนมาก
สนับสนุนการกระทำทางคณิตศาสตร์ต่อเมทริกซ์
สนับสนุนการเขียนโปรแกรมแบบวัตถุวิธี (Object Oriented Programming)
มีตัวกระทำต่ออาร์เรย์และเมทริกซ์ให้เลือกใช้จำนวนมาก
มีคำสั่งในการสร้างแผนภูมิอยู่ในตัวภาษา (ไม่ใช่เป็นส่วนผนวก)

เป็นภาษาเปิดซอร์สที่นำไปใช้ได้ฟรีและเรียนรู้ได้ง่าย เป็นภาษาโปรแกรมมิ่งที่ได้รับความนิยมมากภาษาหนึ่ง

ตารางเก็บข้อมูลการสั่งซื้อสินค้า

ตารางหลังจากแปลงแล้ว

โค้ดภาษา M แปลงชื่อสินค้า

ตัวอย่างการแปลงข้อมูลสตริงด้วยภาษา M

หัวข้อนี้แสดงโปรแกรมง่าย ๆ ในภาษา M เพื่อให้เห็นว่าโค้ดภาษา M มีหน้าตาอย่างไร

โจทย์คือ สมมุตว่าเรามีตารางอย่างที่เห็นในภาพ “ตารางเก็บข้อมูลการสั่งซื้อสินค้า” และเราต้องการแปลงชื่อสินค้าให้มีตัวนำตัวตามอย่างที่เห็นในภาพ “ตารางหลังจากแปลงแล้ว”
สามารถทำได้โดยใช้คำสั่งอย่างที่เห็นในภาพ “โค้ดภาษา M แปลงชื่อสินค้า”

บรรทัดที่ 3-7 คือโครงสร้างคำสั่ง let
นิพจน์ของ let ทำหน้าที่จัดกลุ่มชุดของค่าที่จะคำนวณ กำหนดชื่อ แล้วนำไปประมวลผลในนิพจน์ย่อยที่ตามหลังคำสั่ง in
ยกตัวอย่างเช่น บรรทัด 11 เราประกาศตัวแปรชื่อ Source ที่จะเก็บค่าที่เกิดจากนิพจน์ Text.Proper("hello world")
ผลลัพธ์คือค่าของ Source จะเป็น "Hello World"

โค้ดบรรทัด 16 ถึง 20 เราใช่คำสั่ง let เพื่อกำหนดขอบเขตการทำงาน สร้างตารางเก็บข้อมูลการสั่งซื้อสินค้าชื่อ Orders
โดยใช้คำสั่ง Table.FormRecords()

บรรทัด 17-19 สร้างเรคคอร์ดรายการสั่งซื้อสี่เรคคอร์ด
บรรทัด 20 ใช้คำสั่ง Table.TransformColumns() เพื่อแปลงค่าคอลัมน์ Item

นิพจน์ในภาษา M

นิพจน์เป็นแกนหลักในการเขียนโค้ดภาษาใด ๆ
ดังนั้นในหัวข้อนี้เราจะมาดูส่วนประกอบและวิธีเขียนนิพจน์ในภาษา M

ในภาพ “ตัวอย่างโค้ดนิพจน์ในภาษา M” บรรทัด 3 เครื่องหมาย // มีไว้เพื่อใส่ข้อความแสดงความเห็น (comment) ที่ตัวแปลภาษาจะไม่นำไปปฏิบัติ
บรรทัดที่ 4-7 คือตัวอย่างชนิดข้อมูลพื้นฐาน ได้แก่ ตัวเลขจำนวนเต็ม ค่าบูลีน ตัวเลขมีทิศนิยม และสตริงตามลำดับ พวกนี้คือตัวอย่างค่าเดี่ยว
บรรทัดที่ 10 และ 11 คือตัวอย่างค่าที่เป็นชุด หรือโครงสร้างข้อมูล บรรทัด 10 คือโครงสร้างข้อมูลแบบลิสต์ (list)
บรรทัดที่ 11 คือโครงสร้างข้อมูลแบบเรคคอร์ด (record) เมื่อนำค่ามาใช้ร่วมกับตัวกระทำ เราจะได้นิพจน์
บรรทัดที่ 14-23 คือตัวอย่างนิพจน์ในภาษา M
บรรทัดที่ 16 คือนิพจน์คณิตศาสตร์ เลข 1 และ 2 คือตัวถูกกระทำ (operand) เครื่องหมายบวกคือตัวกระทำ (operator)
ขอให้สังเกตว่านิพจน์คณิตศาสตร์ของภาษา M เหมือนภาษา C ที่เป็นแบบตัวกระทำอยู่กลาง (infix) ซึ่งเป็นแบบที่นิยมใช้กันเป็นปรกติทั่วไปในภาษาส่วนใหญ่

บรรทัดที่ 17 คือลิสต์ที่มีสมาชิกสามตัว
บรรทัดที่ 18 คือเรคคอร์ดที่มีสมาชิกสองตัวคือ x และ y
บรรทัดที่ 20 คือตัวอย่างการนิยามฟังก์ชันที่หาผลบวก
บรรทัดที่ 21 แสดงตัวอย่างนิพจน์เงื่อนไข
บรรทัดที่ 22 นิพจน์ของคำสั่ง let
บรรทัดที่ 23 คือโค้ดทำหน้าที่แสดงเออเรอร์เป็นข้อความว่า A

ตารางก่อนและหลังใส่ร้อยละ

ตัวอย่างโค้ดภาษา M เพื่อการหาร้อยละของผลรวม

หาร้อยและของผลรวม

ต่อไปเป็นตัวอย่างโค้ดภาษา M เพื่อการหาร้อยละของผลรวมในเวิร์กชีตของโปรแกรม Microsoft Excel 2016

การป้อนโค้ดให้ป้อนใน Query Editor
วิธีเปิด Query Editor ทำได้โดยกดที่แถบ Data แล้วดูในกลุ่ม Get & Transform เลือก New Query / From other resources / Blank Query

ข้อมูลตัวอย่างคือ ตารางข้อมูลอย่างที่เห็นในภาพ “ตารางก่อนและหลังใส่ร้อยละ”
ตารางบนชื่อ PercentageShareOfTotalInput

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

ในภาพ “ตัวอย่างโค้ดภาษา M เพื่อการหาร้อยละของผลรวม”

บรรทัดที่ 5 ประกาศตัวแปรชื่อ Source ทำหน้าที่ดึงข้อมูลมาจากตารางที่อยู่ในเวิร์กชีตที่กำลังทำงานอยู่ในปัจจุบัน
บรรทัด 8 สร้างตัวแปรชื่อ GroupedRow ทำหน้าที่เก็บตารางที่ได้จากการจัดกลุ่มข้อมูลของ Source
บรรทัด 9 สร้างคอลัมน์ใหม่ชื่อ GrandTotal โดยนำค่ามาจากผลรวมของคอลัมน์ Sales
บรรทัด 11 ประกาศตัวแปร GrandTotal เพื่อเก็บข้อมูลจากคอลัมน์ Grandtotal

บรรทัด 13 ประกาศตัวแปรชื่อ Custom1 โดยนำข้อมูลมาจาก Source
บรรทัด 15 ประกาศตัวแปรชื่อ InsertedCustem ตัวแปรนี้เก็บตารางที่เกิดจาก Custom1 และแทรกคอลัมน์ใหม่ชื่อ Share ที่มีค่าของคอลัมน์ Sales ใน Custom1 หารกับค่าในตัวแปร GrandTotal

โค้ดตัวอย่าง เรคคอร์ด ฟิลด์ และตาราง

ตารางจากโค้ดบรรทัด 10

เรคคอร์ด ฟิลด์ และตาราง

การประมวลผลข้อมูลใน Power BI และ Excel เรามักจะใช้โครงสร้างข้อมูลแบบตารางอยู่เสมอ

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

การเขียนชื่อฟิลด์ไม่ต้องใส่เครื่องหมายคำพูด ในภาพ “โค้ดตัวอย่างเรคคอร์ดฟิลด์และตาราง”

บรรทัดที่ 4 ถึง 8 คือเรคคอร์ดที่ประกอบด้วยฟิลด์สามฟิลด์มีชื่อ A B และ C แต่ละฟิลด์มีค่า 1 2 และ 3 ตามลำดับ

บรรทัดที่ 10 คือคำสั่งที่จะสร้างตารางอย่างที่เห็นในภาพ “ตารางจากโค้ดบรรทัดที่ 10” ซึ่งประกอบด้วยคอลัมน์และแถว

ในภาษา M เราสร้างตารางจากฟังก์ชันมาตรฐานที่มีอยู่หลายตัว โดยจะสร้างจากลิสต์หรือเรคคอร์ดก็ได้ หน้าตาของตารางที่เกิดจากโค้ดนี้จะเป็นอย่างที่เห็นในภาพ “ตารางจากโค้ดบรรทัด 10”

สูตรและผลลัพธ์ในเวิร์กชีต

เราสามารถเขียนคำสั่งภาษา M เลียนแบบสูตรใน Execl ได้
อย่างเช่นสูตรที่เห็นในภาพ “สูตรและผลลัพธ์ในเวิร์กชีต” สามารถเขียนเป็นภาษา M ได้อย่างบรรทัด 12-16

ตารางที่อยู่ทางซ้าย คือ สูตร
ตารางทางขวา คือ ผลลัพธ์การคำนวณของสูตร

นิพจน์ในภาษา M สามารถอ้างถึงนิพจน์อื่นได้โดยอาศัยชื่อ และลำดับของกระบวนการหาค่าจะถูกกำหนดโดยลำดับของการอ้างอิงโดยอัตโนมัติ
ดังนั้นเราจึงสามารถใช้เรคคอร์ดเพื่อสร้างนิพจน์ที่เทียบเท่ากับสูตรที่เห็นในภาพ “สูตรและผลลัพธ์ในเวิร์กชีต” และผลลัพธ์จะเทียบเท่ากับเรคคอร์ในบรรทัด 18-22

เราสามารถทำเรคคอร์ดซ้อนเรคคอร์ดได้ และใช้ตัวกระทำ [ ] “มองหาค่า” (Lookup operator) เพื่อเข้าถึงฟิลด์ภายในเรคคอร์ที่ต้องการด้วยชื่อ

ยกตัวอย่างเช่น
โค้ดบรรทัด 25,26 คือ เรคคอร์ดที่มีฟิลด์ชื่อ Sales โดยที่ฟิลด์นี้เป็นเรคคอร์ดด้วยเหมือนกัน
และ มีฟิลด์ชื่อ Total ที่อ้างถึงฟิลด์ FirstHalt และ SecondHalf
และเนื่องจากทั้งสองฟิลด์นี้เป็นฟิลด์ที่อยู่ภายในเรคคอร์ด Sales
การอ้างถึงจึงใช้ตัวกระทำ [ ] โดยนำหน้าด้วยชื่อเรคคอร์และใส่ชื่อฟิลด์ไว้ภายในตัวกระทำ [ ]
เมื่อสูตรนี้ถูกคำนวณแล้วจะมีผลลัพธ์เท่ากับโค้ดบรรทัด 29-32

เรคคอร์ดกับลิสต์

ภายในเรคคอร์ดอาจมีฟิลด์เป็นลิสก์ก็ได้ และ เราสามารถอ้างถึงรายการในลิสต์ได้โดยใช้ตัวกระทำ “ดรรชนีระบุตำแหน่ง” { } (positional index operator)

โค้ดบรรทัด 34-51 คือ ตัวอย่างเรคคอร์ดที่มีฟิลด์สองตัว
ตัวแรกคือ Sales ที่มีภาวะเป็นลิสก์
ตัวที่สองคือ TotalSales ที่เป็นตัวแปรธรรมดา

โปรดสังเกตว่าภายในลิสก์ Sales มีหน่วยข้อมูลอยู่สองชุด
ชุดแรกคือบรรทัดที่ 37-42 เป็นเรคคอร์ด และ
ตัวที่สองคือบรรทัดที่ 43-48 ก็เป็นเรคคอร์ดด้วยเช่นกัน
เรคคอร์ดทั้งสองมีจำนวนฟิลด์และชื่อฟิลด์เหมือนกัน
เนื่องจากชื่อเหมือนกัน เมื่อ เราต้องการอ้างถึง เราจะต้องระบุดรรชนีจึงจะสามารถอ้างได้ถูกตัว
ยกตัวอย่างเช่น
ถ้าต้องการอ้างถึงฟิลด์ Total ในบรรทัด 41 จะต้องเขียนว่า Sales{0}[Total]
แต่ถ้าต้องการอ้างถึงฟิลด์ Total ในบรรทัด 47 จะต้องเขียนว่า Sales{1}[Total]

Lazy evaluation

นิพจน์ทั่วไปในภาษา M จะถูกหาค่าทันที
ยกเว้นนิพจน์ต่าง ๆ ที่อยู่ภายในสมาชิกของเรคคอร์ดและลิสต์ (รวมถึงนิพจน์ต่าง ๆ ภายในคำสั่ง let) ที่จะถูกหาค่าโดยวิธี “ประเมินช้า” (Lazy evaluation)
ซึ่งจะหาค่าเมื่อถึงเวลาที่จำเป็นต้องใช้ค่านั้น ๆ ตรงข้าม กับ วิธีหาค่าแบบ “ประเมินก่อน” (eager evaluation)
ซึ่งเป็นการหาค่าทันทีโดยไม่คำนึงว่าจะใช้งานค่านั้นเมื่อใด อันเป็นการทำงานตามปรกติในคำสั่งที่ไม่เกี่ยวข้องกับลิสต์และเรคคอร์ด

วิธีเข้าใจการทำงานแบบ Lazy evaluation คือให้จำไว้ว่าการคำนวณค่าที่เกี่ยวข้องกับลิสต์และเรคคอร์ดจะยังไม่เกิดขึ้นจนกว่าเราจะเรียกหามัน (เช่นเมื่อให้นำค่าไปใส่ในตัวแปร)
และผลลัพธ์ของการหาค่าจะเกิดจากค่าของลิสต์หรือเรคคอร์ดที่กระทำต่อสมาชิกตัวที่ถูกระบุโดยตัวกระทำ “มองหาค่า” และตัวกระทำ “ดรรชนีระบุตำแหน่ง” อย่างที่เห็นในบรรทัดที่ 50