คัมภีร์สรุป M Code ใน Power Query ตอนที่ 2 : Function และ each

ในตอนที่แล้วเราได้เรียนรู้พื้นฐานของการใช้ M Code กันไปแล้ว คราวนี้เราจะมาเรียนรู้ถึงสิ่งที่ทรงพลังที่สุดของ M Code นั่นก็คือเรื่องของหลักการของการใช้ Function นั่นเอง

เมื่อคุณใช้งาน Power Query ไปเรื่อยๆ จุดที่บ่งบอกว่า คุณกำลังจะก้าวไปสู่ความสามารถอีกขึ้นหนึ่งของ Power Query ก็คือ ความสามารถในการใช้งาน Custom Function นี่แหละครับ ซึ่งหากใช้เป็น คุณจะสามารถแก้ไขปัญหาที่ซับซ้อนได้มากขึ้นกว่าการใช้เครื่องมือมาตรฐานมากมายหลายเท่าเลย

Function คือ ขุมพลังที่แท้จริงของ M Code

Function (ฟังก์ชัน) คือ สิ่งที่สามารถรับค่า input เข้ามาคำนวณประมวลผล แล้วส่งผลลัพธ์ output ออกมาได้

รูปแบบ

(input1,input2,...) => expression วิธีคำนวณหรือสูตรของฟังก์ชันนั้นๆ

เช่น จะสร้างฟังก์ชันของตัวเองขึ้นมา (เรียกว่า Custom Function) เอาไว้หาพื้นที่สามเหลี่ยม

(ฐาน,สูง) => 0.5*ฐาน*สูง   //เราตั้งชื่อ input เป็นภาษาไทยก็ได้นะ แต่ผมแนะนำให้ตั้งเป็น eng แหละดีแล้ว

การจะเข้าใจเรื่องนี้ได้ดีขึ้น ผมอยากให้นึกถึงฟังก์ชันของ Excel เป็นตัวเปรียบเทียบครับ ยกตัวอย่างเช่น ฟังก์ชัน LEFT ใน Excel มีรูปแบบดังนี้

=LEFT(text,num_chars) //จะเห็นว่ามี input 2 ตัวคือ text และ num_chars

ซึ่งถ้าเราใส่ input 2 ตัวนี้เข้าไป LEFT ก็จะทำการดึงบางส่วนของข้อความ text ออกมาจากทางซ้าย ด้วยจำนวนตัวอักษระที่ระบุใน num_chars เช่น =LEFT(“abcde”,2) จะได้ผลลัพธ์เป็น 2 ตัวซ้าย นั่นคือ “ab” เป็นต้น

หน้าที่ของฟังก์ชัน

รับค่า input →  เอาไปทำอะไรซักอย่าง (processing) → แล้วคายค่าผลลัพธ์ output ออกมา

สามารถระบุประเภท Data Type ได้

(input1 as text ,input2 as number) as text => expression

สามารถกำหนด input เป็น optional ได้

(input1 as text ,optional input2 as number) as text => expression 

ซึ่งตัวที่เป็น optional หากไม่ใส่ค่ามาจะมีค่าเป็น null ดังนั้นเราอาจต้องกำหนดว่า ถ้าค่า input2 เป็น null จะให้ทำอะไรแทน เป็นต้น

การเรียกใช้ Custom Function

สมมติว่าเราสร้าง Query ชื่อ TriangleArea ขึ้นมาด้วย M Code ว่า

(ฐาน,สูง) => 0.5*ฐาน*สูง

เวลาเราจะใช้งาน ก็สามารถเรียกใช้ได้ใน M Code ได้เลย เช่น ให้สร้าง Blank Query อีกอันนึงในไฟล์เดิม แล้วใส่ M Code ว่า

หรือจะใช้ผ่าน Invoke Custom Function ก็ได้ เช่น

ซึ่งมันก็จะออกมาเป็นแบบนี้

การใช้ each หรือ ฟังก์ชันแบบย่อๆ

เคยเห็น each ที่โผล่มาตอนเรียกคำสั่งต่างๆ มั้ย?

หลายๆ ครั้งเวลาที่เรากดคำสั่งเมนูมาตรฐานมันก็จะใส่ each ให้เราเอง เช่น ในตัวอย่างข้างบนก็มี each หรือว่าหากกด Add Column → Custom Column ก็จะมี each ซึ่งผมจะอธิบายโดยละเอียดให้เห็นชัดๆ ดังนี้ว่าจริงๆ แล้วมันคืออะไรกันแน่ครับ

เช่น สมมติเรามีข้อมูล ราคาสินค้า และจำนวนอยู่ แล้วอยากจะหายอดขาย

ปกติแล้วเราก็จะ Add Custom Column ได้แบบนี้

ซึ่งสูตรจะออกมาเป็น = Table.AddColumn(#”Changed Type”, “ยอดขาย”, each [ราคา]*[จำนวน])

แล้วเจ้า each มันคืออะไรล่ะ? เดี๋ยวผมจะอธิบายให้ฟัง

each คือการย่อวิธีเขียนฟังก์ชัน

จากที่เรารู้แล้วว่าเราสามารถใช้ฟังก์ชันลักษณะนี้ได้

(x) => x+1

ดังนั้นเราสามารถเปลี่ยนชื่อตัวแปรจาก x เป็น _ ได้ จะได้ว่า

(_) => _+1

ซึ่งหากใช้ชื่อตัวแปรเป็น _ เราจะสามารถย่อ Code ส่วน (_) => ได้เป็น each ดังนี้

each _+1

และถ้ามีการอ้างควบคู่กับ [ ] ที่เป็น Record lookup operator เช่น จริงๆ แล้ว _  ที่กำลังอ้างถึง คือตัว table หรือ record จะสามารถละตัว _ ทิ้งไปได้เลย เพื่อให้อ่านสูตรแล้วดูง่ายขึ้น เช่น

Table.SelectRows ( Source, (_) => _[Qty] > 100 )

จะย่อได้เป็น

Table.SelectRows ( Source, each _[Qty] > 100 )

และย่อได้อีกว่า

Table.SelectRows ( Source, each [Qty] > 100 )

each ที่โผล่มาเมื่อกด Custom Column

เดี๋ยวผมจะค่อยๆ อธิบายให้ฟังถึงเหตุผลที่ว่า ทำไมเราถึงสามารถอ้างอิงข้อมูลใน Field ที่ต้องการด้วยการใส่ [ ] ครอบลงไปได้เลย แล้วมันรู้ได้ไงว่าควรเอาข้อมูลในบรรทัดไหน

ก่อนอื่น ให้เราลองเปลี่ยนสูตรใน Custom Column ให้เหลือแค่ _ จะเห็นภาพชัดขึ้นครับ

ซึ่งสูตรจะออกมาเป็น

= Table.AddColumn(#"Changed Type", "ยอดขาย", each _ )

แต่จะเห็นว่าในคอลัมน์ยอดขายจะได้ผลลัพธ์ออกมาเป็น Record ในบรรทัดนั้นๆ ซึ่งประกอบไปด้วย Field ทุกอันในตารางเดิมใน Step #”Changed Type”

นั่นแปลว่า _ ใน Step การ AddColumn จะแทน Record ของแต่ละบรรทัดนั่นเอง
และการอ้างอิงแต่ละ Field ของ Record จึงทำด้วยวิธี _[ชื่อ Field]

และการที่เราใช้ _ เป็นชื่อของ input ทำให้สามารถละ _ ตอนอ้างอิงชื่อ Field ได้ ทำให้แทนที่จะเขียนเต็มๆ ว่า _[ชื่อ Field] จึงสามารถเขียนแค่ [ชื่อ Field] ได้เลย

แปลว่า ถ้าเราไม่ย่ออะไรเลย สูตรเต็มๆ จะเป็นแบบนี้ครับ

= Table.AddColumn(#"Changed Type", "ยอดขาย", (_)=>_[ราคา]*_[จำนวน]) 

โดยที่ _ ที่เป็น Input ของฟังก์ชันก็คือ Record ในแต่ละบรรทัดนั่นเอง

พอย่อ (_)=> เป็น each จะได้แบบนี้

= Table.AddColumn(#"Changed Type", "ยอดขาย", each _[ราคา]*_[จำนวน]) 

แต่พอเขียนแบบย่อสุดๆ โดยละ _ หน้า [ ] ทิ้ง เราเลยเขียนสูตรได้ว่า

= Table.AddColumn(#"Changed Type", "ยอดขาย", each [ราคา]*[จำนวน])

สรุปวิธีการอ้างอิง Record (แถว), List (ข้อมูลในคอลัมน์) และ Table (ตาราง)

ถ้าจะอ้างอิง Record ปัจจุบันที่เลือกอยู่ เรารู้แล้วว่าสามารถใส่ _ หลัง each ได้เลย

ทีนี้ถ้าเราอยากอ้างถึงอย่างอื่นบ้างล่ะ เช่น จะอ้างถึง Table ทั้งอันเลย คอลัมน์ที่ต้องการ หรือ Record ก่อนหน้า จะทำยังไง? เรามาดูทีละอันครับ

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

Tips : ถ้าอยากได้ Step อื่นก็ใส่ชื่อ Step นั้นๆ ลงไป ถ้าอยากได้ตารางอื่นก็ใส่ชื่อตัวแปรที่เป็นตารางอื่น หรือชื่อ query อื่นลงไป

ถ้าจะอ้างอิงคอลัมน์ที่ต้องการทั้งคอลัมน์ ก็ใส่ชื่อ Table ตามด้วย [ชื่อคอลัมน์] เช่น ใส่ว่า Source[ผลไม้] ผลลัพธ์ก็จะออกมาเป็น List

ถ้าจะอ้างอิงถึงแถวก่อนหน้า แปลว่าเราต้องรู้แถวปัจจุบันของตัวเองก่อนถึงจะง่าย ซึ่งวิธีที่ง่ายที่สุดคือใส่ index column ให้เริ่มตั้งแต่เลข 0 เอาไว้ เช่น

แล้วเราค่อยอ้างอิง Table แล้วใส่ row index ที่ต้องการ (index 0 คือ แถวแรกนะ อย่าลืม)

ดังนั้นเราจะใส่ใน Custom Column ว่า =Source{[Index]-1} แบบนี้ได้ครับ

ทีนี้แถวแรกมัน Error เราจะจัดการยังไงดี?

การจัดการ Error ด้วย try…otherwise…

หากเราอยากจะจัดการ Error ไม่ให้แสดงออกมา เราสามารถใช้คำสั่ง

try...x... otherwise ...y... 

มาช่วยได้ครับ

คำสั่งนี้มีวิธีทำงาน คือ หาก try x แล้วสำเร็จก็จะทำ x ไป แต่ถ้าลอง x แล้ว Error ก็จะทำ y แทน ซึ่งเราอาจจะทำให้ y เป็นอะไรก็ได้ เช่น 0 หรือปล่อยเป็น null ก็นิยมครับ

เช่น ใน Custom Column เราเพิ่มสูตรส่วนของ try และ otherwise ว่า 

=try [ราคา]*[จำนวน] otherwise 0

แบบนี้มันจะเอาคอลัมน์ [ราคา]*[จำนวน] หาก error ให้แสดงค่า 0 แทน

รวมถึงการอ้างอิงแถวก่อนหน้าที่ Error ในแถวแรกสุด (เพราะไม่มีแถวก่อนหน้า) เราอาจจะให้เป็น null ก็ได้ เช่น

try Source{[Index]-1} otherwise null

แค่นี้ก็จัดการ error ที่ไม่ต้องการได้แล้วครับ

การเขียนเงื่อนไขด้วย if

สำหรับการเขียนเงื่อนไขใน M Code เราก็ใช้ if…then…else… ประกอบกับการใช้ and or not ก็ได้ครับ

อย่างตอนเขียนสูตรเพื่ออ้างอิงแถวก่อนหน้า ถ้าไม่ใช้ try…otherwise… เราจะใช้ if ก็ได้ ก็จะให้ผลลัพธ์แบบเดียวกัน

if [Index]=0 then null else Source{[Index]-1}

เจาะลึก each ด้วยการใช้ M Code เลียนแบบ VLOOKUP Approximate Match

สมมติผมมีข้อมูลคะแนนสอบอยู่แล้วต้องการจะตัดเกรด ดังนี้
ผมเอาตารางซ้ายเข้าเป็น Query ชื่อ TestResult ส่วนตารางขวาชื่อ RefGrade

ใน Query Test Result นั้นผมสร้าง Custom Column ด้วยสูตรว่า =RefGrade ก็จะได้ผลลัพธ์แบบนี้ ว่าในแต่ละบรรทัดของคะแนนสอบ เราได้ตาราง RefGrade กลับมาทั้งตารางเลย (ซึ่งเยอะไป!)

สิ่งที่เราต้องการจริงๆ คือ อยากดูว่าคะแนนที่ได้นั้นอยู่ในช่วงไหน ซึ่ง logic ที่ใช้ได้คือ ต้องทำการเลือก RefGradeมาเฉพาะบรรทัดที่คะแนนต่ำสุด น้อยกว่าหรือเท่ากับ คะแนนสอบที่ได้ในแต่ละบรรทัดเท่านั้น และค่อนเลือกเอาบรรทัดสุดท้ายมาฃ

ซึ่งเราจะพยายามเลือกให้เหลือเฉพาะบรรทัดที่ คะแนนต่ำสุด น้อยกว่าหรือเท่ากับ คะแนนสอบที่ได้ในแต่ละบรรทัดก่อน ด้วยฟังก์ชันที่ชื่อว่า Table.SelectRows ซึ่งมีวิธีการใช้ดังนี้

Table.SelectRows(table as table, condition as function) as table

จะเห็นว่า input ที่มันต้องการมี 2 ตัว คือ table ต้นฉบับ และ เงื่อนไข ซึ่ง table ต้นฉบับนี่ง่ายมาก ก็คือ RefGrade นั่นแหละ
แต่ที่ยากก็คือเจ้า condition ที่ดันต้องใส่เป็นฟังก์ชันด้วยสิ

เพื่อที่จะให้เข้าใจว่าปกติมันทำงานยังไง เราจะไปดู Query RefGrade แล้วไปลอง Filter มันเล่นๆ ดูก่อน ด้วยเงื่อนไขว่า คะแนนต่ำสุด <= 69 จะได้แบบนี้

ซึ่งเราจะเห็นฟังก์ชัน Table.SelectRows ทำงานให้เราดูเลยว่าต้องเขียนยังไง

= Table.SelectRows(#"Changed Type", each [คะแนนต่ำสุด] <= 69)

เราก็เลยคิดว่าจะเอาคำสั่งนี้กลับไปเขียนใน TestResult ที่เราค้างไว้ได้ ซึ่งมันก็ใช้ได้จริงๆ (แค่เปลี่ยนชื่อ table จาก “Changed Type” เป็น RefGrade)

และถ้าเราไม่อยากจะ hardcode เลข 69 ล่ะ?

เราอยากให้แต่ละบรรทัด มันใช้คะแนนสอบไปเป็นเงื่อนไขในการ Filter แทนการพิมพ์เลข 69 ลงไปเอง เราอาจเผลอคิดว่าจะใช้ [คะแนน] แทนได้เลย แต่มันไม่ง่ายแบบนั้นหรอก หึหึ เพราะมันจะบอกว่าหา Field ที่ชื่อว่า คะแนน ไม่เจอ

ทำไมถึงหาไม่เจอ? ก็เป็นเพราะภายใต้ each อันที่สอง มันกำลังมอง Record ของตาราง RefGrade อยู่น่ะสิ ซึ่งมันไม่มีคะแนนไง เพราะคะแนนอยู่ในตาราง TestResult ต่างหาก!!

แล้วเราจะไปอ้างอิง Field ที่อยู่ใน Current Record ของ TestResult ได้ยังไง? คราวนี้แหละเราจะไม่ใช้การย่อด้วย each แล้วเพราะมันทำให้เกิดการอ้างอิงที่ซ้ำกันแล้วโปรแกรมมันก็งง เราจะตั้งชื่อ input ด้วยตัวเราเองให้ชัดเจนไปเลยจะได้ไม่งง โดย Current Record ของ TestResult ผมจะตั้งชื่อว่า main และแต่ละ row ของ RefGrade ผมจะตั้งชื่อว่า sub ซึ่งจะแปลงสูตรได้ดังนี้

จากเดิมที่เขียนแล้วไม่ชัดเจน จึงมีปัญหา

= Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(RefGrade, each [คะแนนต่ำสุด] <= [คะแนน]))

เปลี่ยนใหม่ ให้ชัดเจนขึ้นว่าอ้างถึง Field ของ Record ไหน

= Table.AddColumn(#"Changed Type", "Custom",  (main) => Table.SelectRows(RefGrade, (sub)=> sub[คะแนนต่ำสุด] <= main[คะแนน]))

คราวนี้ผลลัพธ์ไม่ Error แล้ว

แต่จะเห็นว่ามันให้ผลลัพธ์กลับมาหลายบรรทัดอยู่ เราจะเอาแค่บรรทัดสุดท้าย ก็ใช้ Table.Last ได้ ดังนี้

= Table.AddColumn(#"Changed Type", "Custom", (main) => Table.Last(Table.SelectRows(RefGrade, (sub)=> sub[คะแนนต่ำสุด] <= main[คะแนน])))

ผลลัพธ์ออกมาเป็น Record เดียวแล้ว ก็กด expand ที่มุมขวาบนของ Field ได้เลย จะได้ผลลัพธ์สุดท้ายที่สมบูรณ์ดังรูป

ตอนต่อไป

ตอนนี้เราก็ได้เรียนรู้การใช้ฟังก์ชันเบื้องต้นกันไปแล้ว เดี๋ยวในตอนต่อไปจะป็นเรื่องของ List แบบลึกซึ้งขึ้น ซึ่งมีประโยชน์มากด้วยแน่นอนครับ ติดตามต่อได้เลย