คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1

ซีรีส์นี้จะเป็นการเอาเนื้อหา M Code Language ของ Power Query บางส่วนในหนังสือ Excel Power Up และการเรียนรู้เพิ่มเติมของผมหลังจากนั้นมาผสมผสานกัน เนื้อหาจึงมีทั้งส่วนที่เหมือนและแตกต่างกับในหนังสือด้วยนะครับ อีกอย่างในหนังสืออาจ Copy Code ลำบาก ผมเลยเขียนเป็นบทความไว้ให้เลยดีกว่า จะได้ลอง Copy Paste สูตรไปเล่นได้ง่ายๆ ด้วย

เอาเป็นว่าใครอยากจะลงลึกเรื่อง M Code ก็อ่านซีรีส์นี้ได้เลย เพราะผมอยากให้มีคนไทยใช้เครื่องมือนี้เป็นเยอะๆ ครับ อีกอย่างมันใช้ได้ทั้งใน Excel และ Power BI เลย จึงมีประโยชน์มากๆ

และที่สำคัญ ในซีรีส์นี้ผมจะมาอัปเดทเนื้อหาเพิ่มให้เรื่อยๆ ด้วยนะ ติดตามไปกันได้ยาวๆ เลยครับ

M Language คืออะไร?

M Language เป็นภาษาในรูปแบบฟังก์ชัน (Functional Language ตรงข้ามกับอีก concept คือ Object-oriented Programming) ที่ออกแบบมาเพื่อจัดการและดัดแปลงข้อมูลซึ่งเป็นภาษาของ Power Query โดยเฉพาะ ซึ่งปกติเราจะใช้เครื่องมือมาตรฐานใน Power Query Editor สร้าง M Code ออกมาให้เรา แล้วเราค่อยดัดแปลงมันอีกที แต่มาถึงตอนนี้ เราจะมาทำความเข้าใจ M Code ให้ลึกซึ้งมากขึ้นเพื่อที่ให้สามารถใช้มันจัดการปัญหาที่ซับซ้อนมากขึ้นได้ โดยเฉพาะการเขียน Custom Function ที่ทรงพลังมากๆ

เราจะค่อยๆ ทำความเข้าใจทีละส่วน เริ่มจากพื้นฐานกันครับ

ก่อนอื่นคงต้องทบทวนความจำซักนิดนะครับ ว่า M Language เป็นภาษาที่สนใจเรื่องตัวพิมพ์เล็กพิมพ์ใหญ่ หรือที่เรียกว่า่ Case Sensitive พูดง่ายๆ คือ ถ้าเขียนชื่อฟังก์ชันอะไรผิดแค่เรื่องตัวพิมพ์เล็กพิมพ์ใหญ่ มันก็จะ Error ทันทีเลย (แม้แต่คำว่า let กับ in ก็ต้องตัวพิมพ์เล็กให้ถูกต้อง)

Tips : เพื่อให้การเขียน Code ของเราสะดวกสบายที่สุด ผมแนะนำว่าให้คุณฝึกใน Excel365 หรือ Power BI ครับ เพราะมันจะมี Intellisence มาช่วย Guide การเขียนให้เราด้วย

Let Expression

เวลาเราใช้เครื่องมือมาตรฐานสั่งคำสั่งต่างๆ มันจะมีคำว่า let … in… โผล่มาโดยอัตโนมัติ ในรูปแบบ คือ

let
	ชื่อตัวแปร1=expression1(สูตร),
	ชื่อตัวแปร2=expression2(สูตร),
	ชื่อตัวแปร3=expression3(สูตร)
in
	ชื่อตัวแปรที่ต้องการแสดงผลลัพธ์

ยกตัวอย่างเช่น

let
	Step1=1+2,
	Step2=Step1*2
in
	Step2
//แบบนี้ผลลัพธ์จะได้ 6

ภายใน Let คือ การตั้งชื่อตัวแปรให้สูตร (Expression) โดยที่เราสามารถตั้งชื่อตัวแปร (Variable) กี่ตัวก็ได้ตามต้องการ โดยคั่นด้วยเครื่องหมาย comma เช่นในตัวข้างบน จริงๆ ก็คือเขียนในบรรทัดเดียวกันก็ได้แบบนี้ (และนี่คือสาเหตุว่าทำไมหลัง let ตัวสุดท้ายไม่มี comma เพราะมันใช้คั่นการตั้งชื่อตัวแปร)

let   ตัวแปร1=สูตร1,   ตัวแปร2=สูตร2,   ตัวแปร3=สูตร3

และชื่อตัวแปรแต่ละอันก็จะไปโผล่เป็นชื่อ Step ใน Power Query Editor ด้วยนะ

หลัง in คือ สิ่งที่มันจะทำการคำนวณเพื่อแสดงเป็นผลลัพธ์ เวลามันทำงานมันจะดูว่า “หลัง in เราต้องการผลลัพธ์อะไร” มันจะดูเองว่าจะเอาค่ามาจากไหน

• ในตัวอย่างข้างบน มันจะรู้ว่าเราต้องการค่าในตัวแปร Step2
• มันก็จะไปดูว่า Step2 ต้องใช้ค่าจากตัวแปร Step1 ไปคูณ2
• จากนั้นมันก็จะไปดูว่า Step1 เกิดจากเอาค่า 1+2 ได้ 3
• แล้ว Step2 จะเอา 3*2 ได้ 6

Variable และ Expression

จากในรูปแบบของ M Code จะพบว่า

• สิ่งที่พิมพ์อยู่ก่อนเครื่องหมาย = คือ Variable หรือ ชื่อตัวแปร
• สิ่งที่เราพิมพ์หลังเครื่องหมาย = คือ Expression หรือ สูตร

ซึ่ง Expression จะถูกคำนวณออกมาได้สิ่งที่เรียกว่า Value หรือ ค่าผลลัพธ์ ซึ่งมีได้หลายประเภท

เรามาลงรายละเอียดแต่ละตัวกันครับ

Variable (ชื่อตัวแปร)

ถ้าจะใช้ชื่อตัวแปรแบบอ่านง่ายที่สุด ต้องมีลักษณะดังนี้

1. ต้องขึ้นต้นด้วยตัวอักษร หรือ _
2. ห้ามมีช่องว่างเว้นวรรค คีย์เวิร์ดที่สงวนไว้ หรือ สัญลักษณ์ประหลาด

ถ้าจะผิดกฎดังกล่าว ต้องใส่ชื่อตัวแปรแบบนี้  #”ชื่อตัวแปร” นั่นคือ ใส่ชื่อไว้ในเครื่องหมายคำพูด แล้วขึ้นต้นด้วย Hashtag (ถ้าสังเกตเวลาใช้เครื่องมือ UI มาตรฐาน มันจะตั้งชื่อตัวแปรให้เราแบบนี้แหละ)

let
	Step1=1+2,
	#"Step 2"=Step1 *2,
	Step3=#"Step 2" /3
in
	Step3

ดังนั้น ถ้าไม่อยากให้ชื่อตัวแปรใน M Code ของเรามี # ประหลาดๆ แบบนี้ ก็ให้ตั้งชื่อแบบไม่ต้องเว้นวรรคนะครับ

Expression (สูตร)

คล้ายๆ การเขียนสูตรใน Excel ที่สามารถเป็นส่วนผสมกันระหว่าง Value ประเภทต่างๆ, Operator รวมถึง ฟังก์ชันต่างๆ ทั้ง Standard Function ที่มีมาให้อยู่แล้ว และ Custom Function ที่เราเขียนขึ้นเองได้ด้วย

ซึ่งผลลัพธ์ของสูตรก็จะให้ค่าออกมาเป็น Value เช่นกัน (เหมือน Excel นั่นแหละ) แต่สิ่งที่แตกต่างจาก Excel ปกติคือ Value ของ Power Query สามารถออกมาเป็นค่าเดี่ยวๆ หรือมาเป็นโครงสร้างก็ได้ เช่น List, Record, ตาราง

 ประเภทของ Value (ผลลัพธ์)

ประเภทของ Value ใน Power Query มีดังนี้

ประเภท	รูปแบบ (ระวังตัวพิมพ์เล็กพิมพ์ใหญ่)	ตัวอย่าง
Null (ค่าว่าง)	null	null
Logical (ตรรกะ)	true false	true false
Number (ตัวเลข)	ตัวเลข	0 1 -3 8.5 4.9e-5
Time (เวลา)	#time(hour, minute, second)	#time(06,15,00)
Date (วันที่)	#date(year, month, day)	#date(2019,07,21)
DateTime (วันที่คู่กับเวลา)	#datetime(year, month, day, hour, minute, second)	#datetime(2019,07,21, 06,15,00)
DateTimeZone (วันที่คู่กับเวลา แบบมี TimeZone)	#datetimezone( year, month, day, hour, minute, second, offset-hours, offset-minutes)	#datetimezone(2019,07,21, 06,15,00, 07,00)
Duration (ระยะเวลา)	#duration(day,hour,minute,second)	#duration(1,2,30,0)
Text (ข้อความ)	“ข้อความในคำพูด”	“hello” “สวัสดี”
Binary	ไฟล์/ข้อมูล Binary	#binary(“AQID”)
List (รายการข้อมูล มักใช้กับข้อมูล 1 คอลัมน์)	{value1,value2,value3}	{1, 2, 3}
Record (ข้อมูล 1 แถว)	[field1=value1,field2=value2]	[ A = 1, B = 2 ]
Table	#table( List ของชื่อหัวตาราง , List ของข้อมูล) โดยข้อมูลแต่ละแถวเป็น List อีกทีนึง	#table({“A”,”B”},{{1,2},{3,4}})
Function	(input1,input2,…) => expression ของฟังก์ชันนั้นๆ	(x) => x + 1
Type		type { number } type table [ A = any, B = text ]

เดี๋ยวเราจะมาลงรายละเอียดข้อมูลแต่ละประเภทอีกทีครับ

Operator ต่างๆ ที่ใช้บ่อยๆ

ใน Power Query นั้น Operator ต่างๆ จะทำงานได้เฉพาะกับ Data Type ที่เหมาะสมเท่านั้น เช่น เราไม่สามารถเอาข้อความที่มีหน้าตาเหมือนตัวเลขไปทำการบวกลบกันได้ ซึ่งต่างจาก Excel และ DAX ที่จะพยายาม Convert ประเภทข้อมูลให้เราโดยอัตโนมัติ

ประเภท	เครื่องหมาย	ความหมาย	ตัวอย่าง
คำนวณ	+	บวก	3+2
	–	ลบ	3-2
	*	คูณ	3*2
	/	หาร	3/2
ตัวเชื่อม	( )	ให้คำนวณในวงเล็บก่อน	(3+2)*5
	&	เชื่อมข้อมูล	“Big”&”Cat”
เปรียบเทียบ	=	เท่ากับ	3=2
	<>	ไม่เท่ากับ	3<>2
	>	มากกว่า	3>2
	<	น้อยกว่า	3<2
	>=	มากกว่าหรือเท่ากับ	3>=2
	<=	น้อยกว่าหรือเท่ากับ	3<=2
ตรรกะ	and	และ	x>3 and x<=10
	or	หรือ	x<3 or x>10
	not	กลับจริงเป็นเท็จ เท็จเป็นจริง	not (x>3)

เรื่องที่ควรรู้ของ M Code

Comment

//ถ้าจะ Comment บรรทัดเดียว ทำแบบนี้ (ใส่เครื่องหมาย // นำหน้า)
/*
ถ้าจะ Comment 
หลายบรรทัด
ทำแบบนี้ (ใส่ใน /* ….. */ )
*/

Code จะเรียงยังไงก็ได้!!

เนื่องจากมันจะไปหาเองว่าเราต้องการค่าใน Step2 แล้วมันจะไปดูต่อว่าต้องการค่าใน Step1 ดังนั้นเราจะเรียง Code ยังไงก็ได้ครับ เพราะจริงๆ มันก็เป็นแค่ลำดับของการตั้งชื่อตัวแปรเท่านั้นแหละ

let
	Step2=Step1*2,
Step1=1+2
	
in
	Step2

Return ค่าอะไรออกมาก็ได้

ซึ่งเราจะ Return ค่าอะไรออกมาหลัง In ก็ได้ เช่น ถึงจะเขียน Step2 มาแล้ว แต่เรา Return ค่า Step1 ก็ได้

let
	Step1=1+2,
	Step2=Step1*2
in
	Step1

โดยที่หากเราเขียนแบบนี้ Power Query จะหาทางเองว่าต้องใช้ตัวไหนบ้างในการรัน แล้วมันจะไม่รันตัวที่ไม่จำเป็นด้วย! ซึ่งในตัวอย่างข้างบนมันจะดูว่า สุดท้ายแล้วต้องการผลลัพธ์จากตัวแปร Step1 ซึ่งคำนวณจาก เอาค่า 1+2 ดังนั้นมันจะไม่เสียเวลาไปคำนวณ Step2 เลยครับ หรือถ้าเอาแบบเห็นชัดๆ เลย คือ

let
	Step1=1+2,
	Step2=Step1*2
in
	4+5

แบบนี้ผลลัพธ์จะได้ 9 ซึ่งเกิดจาก 4+5 เลย โดยไม่เกี่ยวกับ Step1 และ Step2 ด้วยซ้ำครับ !!

let in ซ้อน let in ได้อีก

let
	Step1=1+2,
	Step2=Step1+3
	Step3=
		let
			Step1 = 100
			Step2 = Step1*2
		in
			Step2
in
	Step3

แบบนี้จะได้ผลลัพธ์ออกมาเป็น 200 เนื่องจาก Step3 สุดท้ายจะไปอ่านค่า Step2 ที่อยู่ใน Let ชั้นในว่า

Step2 = Step1*2 // ซึ่งจะได้ Step2 = 100*2 ซึ่งจะได้ 200

ดังนั้นจะเห็นว่าเราสามารถตั้งชื่อ Step ซ้ำกันได้ ถ้าอยู่ใน let คนละ scope กันครับ

ค่า Value ที่เป็น Output ของ Expression

ใน Power Query มีได้หลายแบบ ทั้งที่ให้ค่าเดี่ยวๆ และ ค่าเป็นโครงสร้าง ดังนี้

Primitive Value : ข้อมูลพื้นฐานตัวเดียวเดี่ยวๆ

พวกนี้เราได้เรียนรู้กันไปแล้วในตารางข้างบนเนอะ

• Null (ค่าว่าง)
• Logical (ตรรกะ)
• Number (ตัวเลข)
• Time (เวลา)
• Date (วันที่)
• DateTime (วันที่คู่กับเวลา)
• DateTimeZone (วันที่คู่กับเวลา แบบมี TimeZone)
• Duration (ระยะเวลา)
• Text (ข้อความ)

Structured Values : ข้อมูลที่ประกอบกันเป็นโครงสร้าง

List : รายการข้อมูล

เป็นการเอาข้อมูล Primitive มาเรียงกัน อยู่ในเครื่องหมาย { } คั่นด้วย comma

มักถูกใช้เป็นข้อมูล 1 คอลัมน์ใน Table ที่มีหลายๆ ค่า

รูปแบบ

{value1, value2, value3 }

ตัวอย่าง

• {1, 2, 3} // list ตัวเลข 1 2 และ 3
• { 1..5 } // มีค่าเท่ากับ { 1, 2, 3, 4, 5 }
• {“d”..”g”} // มีค่าเท่ากับ { “d”, “e”, “f”, “g”}
• {“D”..”G”} // มีค่าเท่ากับ { “D”, “E”, “F”, “G”}
• {100, true, “A”}

รวม List ได้ด้วย &

{ 1, 2 } & {3, 4 } // { 1, 2, 3, 4 }

 { 1, 2 } & { 3, 2} // { 1, 2, 3, 2 }

อ้างอิงข้อมูลใน List ด้วย {เลข index}

โดยที่ เลข index จะเริ่มต้นด้วยเลข 0 (itemแรก คือลำดับที่ 0 นะ ระวังให้ดี!!)

SampleList = { 10, 20, 30 }

ลองคลิ๊กขวาแล้ว Drill Down ที่ 20 ดู จะได้ออกมาเป็น

SampleList{1} // จะได้ 20  และสังเกตว่าถ้าจะอ้างอิง item ลำดับที่ 2 เลข index จะเป็นเลข 1 นะ

Record : เป็นกลุ่มของ Field

คำว่า Field หมายถึงข้อมูลที่เป็นคู่กันระหว่าง name และ value

มักถูกใช้เป็นข้อมูล 1 แถวใน Table ที่มีหลายๆ คอลัมน์

รูปแบบ

[ FieldName1=value1, FieldName2=value2, FieldName3=value3 ]

โดยที่ชื่อ Field จะเป็นอะไรก็ได้ เป็นตัวหนังสือ ตัวเลข หรือเว้นวรรคก็ได้ ไม่ต้องใส่เครื่องหมายคำพูดด้วย

ตัวอย่าง

[ A = 100, B = 200, C = 300 ]

รวม Record ได้ด้วย &

[ A = 1 ] & [ B = 2 ] // [ A = 1, B = 2]

ถ้าชื่อ field ซ้ำ จะยึดตัวหลังสุด

[ A = 1, B=2 ] & [ A = 10 ] // [ A = 10, B=2 ]

การอ้างอิงข้อมูลใน Record ด้วย [ชื่อ Field]

SampleRecord = [ First Name = "Sira", Last Name = "Ekabut", Height = 172, Weight = 67 ]

ลองคลิ๊กขวาแล้ว Drill Down ที่คำว่า Sira ใน First Name ดู จะออกมาเป็น

SampleRecord[First Name]    // จะได้ "Sira"

Table : ตาราง

คือข้อมูลที่ถูกจัดให้สามารถมีหลายคอลัมน์ และสามารถมีหลายแถวได้ โดยแต่ละคอลัมน์จะสามารถอ้างอิงได้ด้วยชื่อคอลัมน์

รูปแบบ (แบบพื้นฐาน)

#table( set ของชื่อหัวตาราง , set ของข้อมูลแต่ละแถวอยู่ภายใน  { } คั่นด้วย comma )

#table( {"col1","col2"} , { { r1c1value, r1c2value} , { r2c1value, r2c2value},...   } )

ตัวอย่าง

SampleTable = #table( {"A", "B"}, { {1, 2}, {3, 4}, {5, 6} } )

หรือจะเขียนเป็นแบบนี้ก็จะดูคล้ายตารางมากขึ้น

SampleTable = #table( 
{"A", "B"}, 
{ 
{1, 2}, 
{3, 4}, 
{5, 6} 
} )

ซึ่งจะได้ผลลัพธ์แบบนี้ครับ

การอ้างอิงข้อมูลใน Table

ถ้าอ้างอิงด้วย table[ ชื่อ Field ] (แบบไม่ต้องมีเครื่องหมายคำพูด)  จะได้ผลลัพธ์เป็น List ที่เป็นรายการของ Field นั้นๆ (มักใช้กับการอ้างอิงข้อมูลใน 1 คอลัมน์ในตาราง) เช่น =SampleTable[B] จะได้ List ข้อมูลในคอลัมน์ B ออกมา

ถ้าอ้างอิงด้วย table{เลข index} จะได้ผลลัพธ์ออกมาเป็น Record ซึ่งเป็นข้อมูลใน 1 แถวของตาราง เช่น =SampleTable{1} จะได้ Record แถวที่ 2 ของ Table ออกมา (อย่าลืมว่า Index แถวแรก เริ่มที่เลข 0)

ถ้าอ้างอิงด้วยทั้ง {index} และ [ชื่อ Field] ซ้อนกัน ก็จะได้ Item นั้นๆ ออกมาเลย (เพราะระบุทั้งแถวทั้งคอลัมน์แล้ว) เช่น ลองกด Drill Down ดูที่เลข 4 จากตอนที่เป็น Table จะได้แบบนี้

= SampleTable{1}[B] // หมายถึงเอาแถวที่สอง (index1 คือแถว 2) และ เอา Field ชื่อ B

ซึ่งเราสามารถอ้างอิง Field ก่อนแถวก็ได้เช่นกัน เช่น = SampleTable[B]{1}

นอกจากนี้เรายังสามารถอ้างอิงจาก Value ใน Field ที่กำหนด เพื่อให้ได้ Record ออกมาได้ด้วย
ด้วยรูปแบบ {[ชื่อ Field = ค่าที่ต้องการ]} เช่น

= SampleTable{[A=3]}

จะได้ Record ที่ Field A มีค่าเท่ากับเลข 3

อย่างไรก็ตาม วิธีอ้างอิงจาก Value จะใช้ได้ก็ต่อเมื่อมี Value นั้นๆ แค่ตัวเดียวใน Table เท่านั้น หากมีค่าซ้ำกันมันจะให้ผลเป็น Error ทันที

Tips : หากเรากด Drill Down ลงไปในช่องที่อยู่ในคอลัมน์ที่เคยสั่ง Remove Duplicates เอาไว้ก่อน จะทำให้ Power Query รู้ว่า Field นั้นมีค่าไม่ซ้ำกันแน่นอน สูตรที่ออกมาจะเป็นลักษณะของการอ้างอิงจาก Value ใน Field ที่กำหนด ได้โดยอัตโนมัติ

เช่น ตารางของผมเคยกด Remove Duplicates คอลัมน์ TXID มาก่อน จากนั้น ผมกด Drill Down ไปที่ Field ราคาต่อชิ้น ในแถวเดียวกับ TXID ที่มีค่าเป็น TX00004 จะได้ผลลัพธ์ออกมาดังนี้

การ Convert ประเภทข้อมูลที่ใช้บ่อย

อย่างที่บอกไปแล้วว่า เวลาจะใช้ Operator หรือฟังก์ชันต่างๆ ใน M Code เรื่องของ Data Type เป็นเรื่องที่ค่อนข้างซีเรียส ดังนั้นเราจะต้องมีการ Convert ประเภทข้อมูลให้ถูกต้องก่อนที่จะทำการคำนวณด้วย

ซึ่งการ Convert ประเภทข้อมูลใน M Code เราจะใช้ฟังก์ชันมาช่วยครับ วิธีการจำทั่วไป คือ จะแปลงเป็นอะไรให้ขึ้นต้นด้วยประเภทนั้น แล้วตามด้วย .From ได้เลย

จาก	เป็น	วิธีการ	ตัวอย่าง
ใดๆ	Number	Number.From(value as any) as number	=Number.From(“0123”) =123 =Number.From(#date(1900,01,01)) =2 สังเกต : วันแรกที่ Power Query แทนด้วยเลข 1 คือวันที่ 31 Dec ค.ศ.1899 นะครับ ซึ่งไม่ตรงกับ Excel ซึ่งเลข 1 คือวันที่ 1 Jan ค.ศ. 1900)
ใดๆ	Text	Text.From(value as any) as text	=Text.From(123) =”123″
ใดๆ	Logic	Logical.From(value as any) as logical	= Logical.From(1) = Logical.From(“True”) = true
ใดๆ	Date	Date.From(value as any, optional culture as nullable text) as nullable date	=Date.From(1) =31/12 ค.ศ.1899 หรือ พ.ศ. 2442 =Date.From(“21 July 2019″,”en-GB”) =21/7 ค.ศ.2019 หรือ พ.ศ. 2562
ใดๆ	DateTime	DateTime.From(value as any, optional culture as nullable text) as nullable datetime	=DateTime.From(“21 July 2019 13:30″,”en-GB”) =21/7 ค.ศ.2019 หรือ พ.ศ. 2562 เวลา 13:30 น.

Tips: ถ้าจะทำเป็นจำนวนเต็ม แล้วจะใช้ Int64 ต้องระวัง เพราะถ้าใช้ Int64 มีการปัดตัวเลขตามหลักคณิตศาสตร์ด้วย

=Int64.From(1234.789)
=1235

ถ้าจะปัดเศษทิ้งไปเลย แนะนำให้ใช้ RoundDown แทน

= Number.RoundDown(1234.789,0)
=1234

แหล่งอ้างอิง M Code เจ๋งๆ

Website

• https://blog.crossjoin.co.uk/category/power-query/
• https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let

YouTube

• https://youtu.be/_zEmTGHV3UM
• https://youtu.be/3uKNNZqBIkg
• https://youtu.be/IQpniTiXQiI
• https://youtu.be/L0Y1KL7o3aQ