บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query

ในบทความนี้ผมจะพาเพื่อนๆ ไปเรียนรู้ M Code ใน Power Query ผ่านตัวอย่าง 10 อัน โดยไล่ระดับตั้งแต่ Basic ที่สุดไปตัวอย่างที่ยากขึ้นเรื่อยๆ เหมือนเป็นการพาขึ้นบันได 10 ขั้นแรกสำหรับการเรียนรู้ MCode ครับ

ซึ่งการใช้ MCode มันจำเป็นสำหรับคนที่เจอปัญหาการ Transform ข้อมูลที่มีความซับซ้อนกว่าปกติ เช่น แต่ละชีทแต่ละตารางข้อมูลหน้าตาไม่เหมือนกัน หรือหัวตารางอยู่ในรูปแบบที่ไม่ถูกต้อง เป็นต้น

ใครพร้อมจะก้าวเดินไปสู่ระดับที่สูงขึ้น ตามผมมาได้เลย!

ป.ล. บทความนี้เหมาะกับคนที่ใช้ Power Query ผ่านเครื่องมือบน User Interface เป็นอยู่แล้วนะครับ ใครที่ยังไม่แม่นพื้นฐานลองไปดู Playlist นี้ได้

วิธีสร้าง Blank Query

โดยให้เพื่อนๆ สร้าง Blank Query ตามรูป (จะ add เข้า Quick Access Toolbar ไว้ก็ดีนะ)

จากนั้นไปที่ Advanced Editor แล้วลองทำตามได้เลย

เอาล่ะ ตัวอย่าง 10 อันแรกจะมีอะไรบ้าง? มาดูกันครับ

ตัวอย่าง 1 : Basic let…in…

• ประกาศตัวแปรด้วย let แล้วเรียกผลลัพธ์ออกมาหลัง in ได้
• ในที่นี้ประกาศตัวแปรชื่อ MyNum, Factor และ Result ออกมา
• เราจะประกาศตัวแปรโดยคั่นด้วย Comma ซึ่งจะเขียนต่อกันหรือแยกบรรทัดก็ได้ (ปกติจะนิยมแยกบรรทัด แบบตัวอย่างด้านล่างมากกว่า)
• ตัวแปรนึงสามารถอ้างอิงค่าจากตัวแปรอื่นได้ เช่น Result อ้างอิง MyNum, Factor
• ในที่นี้เราเรียกเอาตัวแปร Result ออกมาหลัง in เพื่อแสดงผลใน Query นี้

let
    MyNum = 5, Factor=100,  Result=MyNum*Factor
in
    Result

let
    MyNum = 5,
    Factor=100,
    Result=MyNum*Factor
in
    Result

จะเห็นว่าผลลัพธ์ของ Query ได้ออกมาเป็นเลข 500 (ใช่… Power Query สามารถให้ผลลัพธ์ออกมาเป็น Value เดี่ยวๆ ได้ ไม่จำเป็นต้องออกมาเป็นตารางเสมอไป)

ตัวอย่าง 2 : ใส่ Comment

• ใส่ Comment ด้วยการพิมพ์ // แล้วตามด้วย Comment ที่ต้องการ
• ทุกอย่างที่ใส่เป็น Comment จะไม่ถูกประมวลผล
• ประโยชน์คือเอาไว้กลับมาดู Code ตอนหลัง หรือส่งให้คนอื่นทำงานต่อจะได้ไม่งง
• แล้ว comment เลห่านั้นจะมาโผล่เป็นตัว i ใน applied steps ด้วย

let
    MyNum = 5,   //ให้ MyNum มีค่าเป็น 5
    Factor=100, //ให้ MultiplyFactor มีค่าเป็น 100
    Result=MyNum*Factor //เอาตัวแปร 2 ตัวคูณกัน 5*100 = 500
in
    Result //ผลออกมา 500

ตัวอย่าง 3 : เริ่มรู้จัก List

• สร้าง List ได้ด้วย List = {item1,item2,item3}
• item แต่ละอันใน List จะเป็นข้อมูลประเภทไหนก็ได้ ไม่เหมือนกันก็ได้
• เราสามารถอ้างอิงเอาค่าใน List ออกมาได้ด้วย ListName{index} โดยที่ index ของ item ตัวแรก คือ 0
• สร้าง MyList1 ที่ประกอบไปด้วยเลข 4, 29, 70
• สร้าง MyList2 ที่ประกอบไปด้วยข้อความว่า ant, bat, cat
• สร้าง MyList3 ที่ประกอบไปด้วยเลข 1-20
• สร้าง MyList4 โดยรวม List1, List2 และ List3 เข้าด้วยกันด้วย &
• อ้างอิงข้อมูล item ที่เป็นคำว่า bat ใน MyList4 ด้วย List{เลขindex}

let
    MyList1 = {4, 29, 70},
    MyList2 ={"ant", "bat", "cat"},
    MyList3 = {1..20},
    MyList4 = MyList1&MyList2&MyList3
    
in
    MyList4

ลองอ้างอิง item ที่เป็นข้อความว่า bat ด้วย MyList{4} (ตัวที่ 5 คือ index 4 )

ตัวอย่าง 4 : แปลงค่าในคอลัมน์ของตารางให้กลายเป็น List

ที่สำคัญเลย หากเรามีตารางอยู่ เราสามารถอ้างอิงค่าในคอลัมน์ใดคอลัมน์นึ่งให้เป็น List ได้ด้วยการเขียนสูตรในรูปแบบ TableName[ColumnName] เช่น

สมมติว่าเรามีตารางแบบนี้ ชื่อว่า MyData แล้วเราเอาดึงค่าไว้ในตัวแปร MyTable

หาเราเขียนสูตรว่า =MyTable[Quantity] เราจะได้ข้อมูลในคอลัมน์ Quantity ออกมาเป็น List

let
    MyTable = Excel.CurrentWorkbook(){[Name="MyData"]}[Content],
    GetQty = MyTable[Quantity]
in
    GetQty

ตัวอย่าง 5 : ฟังก์ชันสำเร็จรูป

• ดูฟังก์ชันมาตรฐานทั้งหมดได้ที่นี่
• ฟังก์ชันใน MCode จะมีเยอะมากๆ วิธีเลือกฟังก์ชัน คือ มันมักจะขึ้นต้นด้วยประเภทข้อมูลที่เกี่ยวข้อง ตัวอย่างเช่น….
  • ฟังก์ชันเกี่ยวกับ Number เช่น Number.Abs, Number.Mod, Number.Sign,
  • ฟังก์ชันเกี่ยวกับ Text เช่น Text.Lower, Text.Contains, Text.Replace, Text.Split, Text.Combine
  • ฟังก์ชันเกี่ยวกับ List เช่น List.Sum, List.Min, List.Max, List.Distinct, List.Select, List.Transform, List.Zip
  • ฟังก์ชันเกี่ยวกับ Table เช่น Table.ColumnNames, Table.TransformColumnNames, Table.SelectRows, Table.SelectColumns
  • ฟังก์ชันเกี่ยวกับ Date เช่น Date.AddDays, Date.AddMonths, Date.ToText, Date.IsInCurrentYear

let
    MyTable = Excel.CurrentWorkbook(){[Name="MyData"]}[Content],
    GetQty = MyTable[Quantity],
    ListMin = List.Min(GetQty), // คำนวณค่าน้อยสุดของ List
    ListMax = List.Max(GetQty), // คำนวณค่ามากสุดของ List
    Result= ListMax-ListMin //เอาค่ามากสุด ลบ น้อยสุด
in
    Result

จะเห็นว่าผลลัพธ์ออกมาได้เลข 7

ตัวอย่าง 6 : สร้าง Custom Function ง่ายๆ

• นอกจากฟังก์ชันมาตรฐาน ที่มีมาให้เยอะแยะแล้ว เรายังสามารถสร้างฟังก์ชันขึ้นมาใช้เองได้ด้วย ซึ่งประโยชน์คือเอาไว้ใช้ร่วมกับการ Transform ที่มีความซับซ้อนมากขึ้น
• สร้าง Custom Function ขึ้นมาได้ ด้วย (parameter) => function expression
• ฟังก์ชั่นจะรับค่า parameter แล้วส่งเข้าไปใน expression

(MyList as list) as number =>
let
    Result = List.Sum(MyList) // คำนวณผลรวมของ List
in
    Result //แสดงผลรวมออกมา

• สมมติเราตั้งชื่อ Query ว่า GetRange มันจะกลายเป็นชื่อฟังก์ชัน

• เราจะสามารถเรียกใช้ Function ได้ด้วย =GetRange(List)
• เช่น = GetRange(Listที่ต้องการ) จะได้ 7 เช่นเดียวกันกับตัวอย่างก่อนหน้านี้

ตัวอย่าง 7 : เข้าใจคำว่า each

• each คือ ตัวย่อของ (_) =>
• ซึ่งเกิดจากการที่ใช้ฟังก์ชันมี input ตัวเดียว แล้วใช้ชื่อตัวแปร ว่า _
• นั่นคือ (x) => x+5 จะเขียนได้ว่า
  • (_) => _ + 5 ซึ่งจะเขียนได้ว่า
  • each _ + 5
• นั่นคือ (x) => Text.Replace(x,”A”,”B”) จะเขียนได้ว่า
  • (_) => Text.Replace(_,”A”,”B”) ซึ่งจะเขียนได้ว่า
  • each Text.Replace(_,”A”,”B”)

ตัวอย่าง 8 : เข้าใจ each ในบริบทของแต่ละแถวของตาราง

เวลาที่เรากดสร้างคอลัมน์ใหม่ ไม่ว่าจะใช้เครื่องมือ หรือใช้ฟังก์ชัน Table.AddColumn ก็ตาม มันมักจะมีคำสั่ง each โผล่มาด้วยเสมอ เช่น เรากด Add Custom Column เพื่อสร้างคอลัมน์ยอดขายจากตาราง จะได้แบบนี้ (ผมแก้ชื่อคอลัมน์เป็น UnitPrice จะได้อ่านง่ายๆ)

สูตรใน Custom Column เขียนว่า

[UnitPrice]*[Quantity]

แต่สูตใน Step นั้นจริงๆ ออกมาว่า

= Table.AddColumn(#"Removed Columns", "Revenue", each [UnitPrice]*[Quantity])

จริงๆ แล้ว มันคือการย่อของ

= Table.AddColumn(#"Removed Columns", "Revenue", each _[UnitPrice]*_[Quantity])

ซึ่งคือการย่อของ

= Table.AddColumn(#"Removed Columns", "Revenue", (_)=> _[UnitPrice]*_[Quantity])

นั่ยแปลว่าจริงๆ แล้ว ฟังก์ชัน Table.AddColumn มี input ของฟังก์ชันคือ _ แล้วถ้าเราทดสอบว่ามันคืออะไร โดยให้เขียนสูตรว่า _ ไปเลย

เราจะพบว่า _ ใน Table.AddColumn ก็คือ Record ของข้อมูลในแถวนั้นๆ นั่นเอง

ซึ่งการเข้าถึง item ใน Record ทำได้โดย RecordName[FieldName] ดังนั้นเราจึงเข้าถึงค่าของคอลัมน์ต่างๆ ได้ เช่น

• _[Quantity] แปลว่า ให้เอา Field ชื่อ Quantity ของ Record นั้นๆ มา
• ซึ่งเราย่อ _[Quantity] ให้กลายเป็น [Quantity] เฉยๆ ได้
• นี่คือที่มาว่าทำไมเราอ้างอิงคอลัมน์ในแถวตัวเองได้ด้วย [ชื่อหัวตาราง]

ตัวอย่าง 9 : แก้ชื่อคอลัมน์ จาก – เป็น _

• แล้วเราต้องการแก้ชื่อคอลัมน์ในตาราง จากเครื่องหมาย – ให้กลายเป็น _ โดยไม่ต้องแก้ทีละอัน เราสามารถทำแบบนี้ได้
• Table.TransformColumnNames ใช้เพื่อวน Loop แก้ชื่อคอลัมน์ในตารางทีละคอลัมน์ด้วยวิธีอะไรบางอย่างที่เราจะระบุในฟังก์ชัน ซึ่ง
  • ในที่นี้เราระบุว่า each Text.Replace(_,”-“,”_”)
• เราสามารถใช้ Text.Replace เพื่อแทนที่ข้อความได้ (คล้ายฟังก์ชัน SUBSTITUTE ใน Excel) ซึ่งในที่นี้เราสั่งแทน “-” ด้วย “_”
• ซึ่งโดยรวมแปลว่า ให้วน Loop ชื่อคอลัมน์ แล้วแทนค่าชื่อคอลัมน์ที่มี – ด้วย _

let
    Source = Excel.CurrentWorkbook(){[Name="MyData"]}[Content],
    ReplaceColumnName=Table.TransformColumnNames(Source,each Text.Replace(_,"-","_"))
in
    ReplaceColumnName

ตัวอย่าง 10 : เลือกเฉพาะคอลัมน์ที่มี – อยู่

• เราใช้ Table.ColumnNames เพื่อเอาชื่อคอลัมน์ทั้งหมดในตารางออกมาเป็น List
• ใช้ List.Select เพื่อทำให้ List เหลือเฉพาะ item ที่ตรงตาม Criteria
• ใช้ Text.Contains ในการช่วยคัดเลือก item เพื่อส่งไป List.Select อีกที
• ใช้ Table.SelectColumns เพื่อ ลือกเอาเฉพาะคอลัมน์ที่ต้องการจาก List ที่ทำไว้

let
    Source = Excel.CurrentWorkbook(){[Name="MyData"]}[Content],
    OriginalColumnName = Table.ColumnNames(Source), //เอาชื่อคอลัมน์ทั้งหมดออกมาเป็น List
    RequiredColumn=List.Select(OriginalColumnName,each Text.Contains(_,"-")), //ทำให้ใน List เหลือแค่ item ที่มี -
    SelectSpecificColumn=Table.SelectColumns(Source,RequiredColumn) // เลือกเอาเฉพาะคอลัมน์ที่ต้องการจาก List
in
    SelectSpecificColumn

ตอนต่อไป

หลังจากเรามีพื้นฐาน MCode จากบทความนี้แล้ว ในตอนต่อไปก็จะได้เรียนเรื่องซับซ้อนกว่านี้ซักทีครับ ใครสนใจรออ่านได้เลย ไม่นานเกินรอ