เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
M CodeExcel Power Up Content

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

power query m code language

ซีรีส์นี้จะเป็นการเอาเนื้อหา 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 ก็ต้องตัวพิมพ์เล็กให้ถูกต้อง)

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 นั่นแหละ) แค่ว่า Value ของ Power Query สามารถออกมาเป็นค่าเดี่ยวๆ หรือมาเป็นโครงสร้าง เช่น ตาราง ได้เลย

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

ประเภทรูปแบบ
(ระวังตัวพิมพ์เล็กพิมพ์ใหญ่)
ตัวอย่าง
Null (ค่าว่าง)nullnull
Logical (ตรรกะ)true falsetrue 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(“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 ต่างๆ ที่ใช้บ่อยๆ

ประเภทเครื่องหมายความหมายตัวอย่าง
คำนวณ+บวก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”}
คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 1

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

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

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

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

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

SampleList = { 10, 20, 30 }

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

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

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

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

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

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

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

รูปแบบ

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

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

ตัวอย่าง

[ A = 100, B = 200, C = 300 ]
คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 5

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

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

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

[ A = 1, B=2 ] & [ A = 10 ] // [ A = 10, B=2 ]
คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1 6

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

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

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

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

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

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

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} 
} )

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

จากเป็นวิธีการตัวอย่าง
ใดๆNumberNumber.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)
ใดๆTextText.From(value as any) as text=Text.From(123)
=”123″
ใดๆLogicLogical.From(value as any) as logical= Logical.From(1)
= Logical.From(“True”)
= true
ใดๆDateDate.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
ใดๆDateTimeDateTime.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

YouTube

แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 443
  •  
  •  
  •  
  •  
  • 443
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

Leave a Reply