“ผมว่า Power Query มันกวน-teen อ่ะพี่” ตั้มทำเสียง “ทีน” ขึ้นจมูก
“กวน-teen ยังไงเหรอตั้ม?” ผมทำเสียง “ทีน” ขึ้นจมูกเช่นกัน
“พอ Round Up แล้วมันเป็นจำนวนเต็ม แต่ผมต้องการเป็นทศนิยม”
“พี่ว่า Power Query ไม่ได้กวน-teen นะ แต่ต้องมีเทคนิคนิดนึง”
“เทคนิคยังไงหรือ Phee” ตั้มทำเสียง “พี่” ขึ้นจมูก

เพื่อให้เห็นภาพชัดเจน สมมติเรามีข้อมูลแบบนี้

ทำเป็น Table โดยคลิกเซลล์ใด ๆ ในข้อมูลแล้วกด Ctrl+T

นำเข้า Power Query โดยคลิกเซลล์ใด ๆ ใน Table แล้วเลือกเมนู Data/ From Table/ Range

โปรแกรมจะเปิดหน้าต่าง Power Query Editor ขึ้นมา

ต้องการเพิ่มคอลัมน์ Round Up ก็คลิกคอลัมน์ต้นทาง (ในที่นี้คือ Original) จากนั้นเลือก Add Column/ Rounding/ Round Up

ได้คอลัมน์ที่ Round Up ทันที!

แต่.. ปัญหาคือผลลัพธ์เป็นเลขจำนวนเต็ม !

ถ้าต้องการเลขจำนวนเต็ม เคสนี้ถือว่าจบ แต่ตั้มต้องการเลขทศนิยม 2 ตำแหน่ง เคสนี้จึงไม่จบ

“งั้นทำยังไงดี?”

ทางแก้คือต้องปรับสูตร
แต่ก่อนปรับต้องเปิด formula bar ออกมาก่อน โดยคลิกที่เมนู View แล้วติ๊กเลือก Formula Bar จะเห็นสูตรทันที

สูตรใน Formula Bar คือ

= Table.AddColumn(
    #"Changed Type", 
    "Round Up", 
    each Number.RoundUp([Original]), 
    Int64.Type )

สูตรนี้แปลง่าย ๆ ว่า สร้างคอลัมน์ใหม่ (Table.AddColumn) ชื่อ Round Up โดยใช้สูตร Number.RoundUp กับคอลัมน์ Original แล้วกำหนด Data Type ของคอลัมน์ใหม่ให้เป็นจำนวนเต็ม (Int64.Type)

ปัญหาคือตรง Number.RoundUp

Number.RoundUp([Original])

ไม่ได้บอกว่าต้องการทศนิยมกี่ตำแหน่ง เมื่อไม่บอกโปรแกรมเลยคิดเองว่าต้องการเลขจำนวนเต็ม
ทางแก้คือบอกว่าต้องการกี่ตำแหน่ง

แต่ก่อนจะบอก มาศึกษาโครงสร้างของฟังก์ชัน Number.RoundUp กันนิดนึง
โครงสร้างคือ

Number.RoundUp(
  number as nullable number,
    [digits] as nullable number
) as nullable number

number คือตัวเลขที่ต้องการปัดค่า เช่น เคสนี้คือคอลัมน์ [Original]
(การอ้างอิงชื่อคอลัมน์ใน Power Query ต้องใส่วงเล็บก้ามปู ( [ ], square bracket ) ปิดหัว-ท้าย)

[digits] คือ ทศนิยมที่ต้องการ

ในที่นี้ผมใส่ [ ] ครอบ digits เพื่อบ่งบอกว่าอาร์กิวเมนต์นี้เป็น optional แปลว่าจะระบุหรือไม่ระบุก็ได้ ถ้าไม่ระบุค่าดีฟอลต์คือ 0 และได้ผลลัพธ์เป็นจำนวนเต็ม

คำว่า as nullable number แปลง่าย ๆ ว่า เป็น number ที่สามารถเป็นค่าว่างได้

ตั้มต้องการ Round Up แล้วได้เป็นทศนิยม 2 ตำแหน่ง ทางแก้คือระบุ [digits] เป็น 2
หรือเขียนสูตรเป็น

Number.RoundUp([Original],2)

หรือสูตรเต็ม ๆ ของบรรทัดนี้คือ

= Table.AddColumn(
   #"Changed Type", 
   "Round Up", 
   each Number.RoundUp([Original],2),
   Int64.Type )

ได้ผลลัพธ์ตามต้องการ

“บ๊ะ! แก้ง่ายแค่นี้เองหรือพี่”
แล้วตั้มก็คลิก Home/ Close & Load เพื่อโหลดข้อมูลไปที่ Excel

“อ้าว! ทำไมมันไม่แสดงค่าอะไรขึ้นมาเลยล่ะ” ตั้มร้องเสียงหลง

แถมมีข้อความแสดง 10 errors ขึ้นมาอีก มันคืออะไร?

ที่ไม่แสดงค่าใด ๆ เพราะสูตรระบุ Data Type เป็น Int64.Type
Int64.Type แปลว่า จำนวนเต็ม
ถ้าลองกลับไปที่ Power Query สังเกตไอคอนที่คอลัมน์ Round Up เป็นรูป 123 หมายถึง จำนวนเต็ม

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

“งั้นแก้ยังไงดีครับพี่” ตั้มถามตรงประเด็น

ทางแก้มี 2 ทาง

1. แก้โดยคลิกเพิ่มขั้นตอนเปลี่ยน Data Type

ก่อนแก้มาดู Step กันนิดนึง
ณ ตอนนี้โปรแกรมทำไปแล้ว 3 step โดยสเต็ปสุดท้ายคือการเพิ่มคอลัมน์ Round Up

ต้องการเปลี่ยน Data Type นั้นง่ายมาก
คลิกไอคอน 123 (ที่คอลัมน์ Round Up) จากนั้นเปลี่ยนเป็นไอคอน 1.2 Decimal Number เพื่อกำหนดให้คอลัมน์นี้เป็นเลขที่มีทศนิยม (สูงสุด 15 ตำแหน่ง)

ได้ผลลัพธ์เป็นเลขทศนิยม !
สังเกตว่าไอคอนเปลี่ยนจาก 123 เป็น 1.2

วิธีนี้ง่ายและสะดวกมาก
ข้อเสียคือมีสเต็ปการเปลี่ยน Data Type เพิ่มขึ้นมา (Change Type1)

ถ้าไม่คิดมากก็ไม่เป็นไร
แต่ถ้าคิดมาก การเพิ่มสเต็ปทำให้คำนวณช้าลง ถ้าลดสเต็ปได้ควรลด

“แล้วจะลดยังไง?”

นั่นคือวิธีที่ 2

2. แก้โดยเปลี่ยนโค้ด

ขอกลับมาที่ step 3 อีกครั้ง

จะพบว่าสูตรของ Step นี้คือ

= Table.AddColumn(
    #"Changed Type", 
    "Round Up", 
    each Number.RoundUp([Original],2), 
    Int64.Type )

สังเกตคำว่า Int64.Type คือการระบุว่าคอลัมน์นี้เป็นจำนวนเต็ม

ถ้าต้องการให้คอลัมนนี้เป็นตัวเลขที่มีทศนิยม ให้เปลี่ยน Int64.Type เป็น type number

= Table.AddColumn(
    #"Changed Type", 
    "Round Up", 
    each Number.RoundUp([Original],2),
    type number )

ระวัง! ต้องสะกดแบบนี้เป๊ะ ๆ (type number) เพราะ Power Query เป็น Case Sensitive

จะพบว่าไอคอนถูกเปลี่ยนเป็น 1.2 (ทศนิยม) โดยไม่ต้องเพิ่มสเต็ปใด ๆ

“ว้าว! แบบนี้เท่ดีอ่ะ งั้นขอถามต่อนิดนึงได้ไหมครับ” ตั้มเกิดไอเดียบรรเจิด
“ว่ามาเลยตั้ม”
“ถ้าผมอยากเปลี่ยนชื่อคอลัมน์จาก “Round Up” เป็น “PQ Round Up” สามารถเปลี่ยนตรง ๆ ในสูตรได้เลยไหมพี่?”
“ถูกต้องนะคร๊าบ” ผมทำนิ้วชี้กับกับนิ้วโป้งเป็นมุมฉาก

= Table.AddColumn(
    #"Changed Type",
    "PQ Round Up",
    each Number.RoundUp([Original],2),
    type number )

ผลลัพธ์ที่ได้คือ

และแน่นอนว่าสเต็ปยังคงเท่าเดิมคือ 3

สูตรนี้ไม่ยากเลยถ้าเข้าใจฟังก์ชัน Table.AddColumn

Table.AddColumn คือฟังก์ชันที่ใช้สร้างคอลัมน์ใหม่
โครงสร้างของฟังก์ชัน Table.AddColumn คือ

Table.AddColumn(
   table as table,
   newColumnName as text,
   columnGenerator as function,
   columnType as nullable type
) as table

table คือตาราง ในที่นี้ใช้เป็นชื่อสเต็ปก่อนหน้า ( #”Changed Type” )

newColumnName คือ ชื่อคอลัมน์ใหม่ที่ต้องการ ในที่นี้ระบุเป็น “PQ Round Up”

columnGenerator คือฟังก์ชันที่ใช้สร้างคอลัมน์ใหม่ ในที่นี้คือ
each Number.RoundUp([Original],2)
การใส่ each ใช้บ่งบอกว่าเป็นฟังก์ชัน และทำแบบนี้ (Number.RoundUp) ทุกบรรทัด

columnType คือ Data Type ในที่นี้ระบุเป็น type number

การที่มี as table ต่อท้าย ( Table.AddColumn(…) as table ) เพื่อบ่งบอกว่าผลลัพธ์ของฟังก์ชันนี้คือ Table

ในขณะที่ฟังก์ชัน Number.RoundUp(…) ต่อท้ายเป็น as nullable number เพื่อบ่งบอกว่าผลลัพธ์ของฟังก์ชันนี้เป็นตัวเลขที่สามารถเป็นค่าว่างได้

สูตรใน Power Query (M Code) ไม่เหมือน Excel สิ่งที่แตกต่างกันมากคือ Data Type
Power Query สนใจว่าข้อมูลนั้นเป็น Data Type อะไร ในขณะที่ Excel อาจมองข้ามหรือไม่สนใจ

จากประสบการณ์ตรง ความผิดพลาดที่พบบ่อยที่สุดใน Power Query เกิดจากการระบุ Data Type ผิดประเภท ถ้าเข้าใจจุดนี้จะลดความผิดพลาดได้เยอะมาก

“ว้าว! ได้ความรู้เยอะเลยพี่” ตั้มโห่ร้อง
“บอกแล้วว่า Power Query ไม่ได้กวน-teen” ผมยิ้ม
“อ๊ะ! คำว่า กวน-teen ไม่ใช่ไม่ดีนะพี่ หมายถึงเป็นญาติกับ กวน-อู”
“เจ็บสีข้างไหมน้อง?”
“นิดนึง ^^” ตั้มยิ้มแล้วเอามือลูบเอว

สำหรับใครที่สนใจไฟล์ตัวอย่างของบทความนี้ ดาวนโหลดเพื่อศึกษาเพิ่มเติมได้เลยครับ

อ้อ! ถ้าอยากรู้เทคนิคการ Round (แบบธรรมดา) ของ Power Query อ่านรายละเอียดได้จาก ep.1

ถ้าคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่โดยคลิก Like เฟซบุ๊กเพจ วิศวกรรีพอร์ต
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^