ผมมีเรื่องสำคัญมากจะบอก
เรื่องสำคัญที่ว่าคือ Power Query คำนวณผิด !

ผิดยังไง?

ผิดถ้าใช้ Round ตัวเลข

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

ถ้าใช้ ROUND ใน Excel แล้วเขียนสูตรเป็น

= ROUND(A2,2)

จะได้ผลลัพธ์แบบนี้

Round แบบนี้ตรงกับหลักประมาณค่าที่เราคุ้นเคย นั่นคือ
ตั้งแต่ 5 ปัดขึ้น
น้อยกว่า 5 ปัดทิ้ง

1.683 ถูกปัดเป็น 1.68
2.375 ถูกปัดเป็น 2.38
3.685 ถูกปัดเป็น 3.69

คราวนี้มาลองใช้ Round ใน Power Query กันบ้าง
เริ่มจากทำข้อมูลให้เป็น Table โดยคลิกที่ข้อมูลแล้วกด Ctrl+T

นำ Table ที่สร้างเข้าไปใน Power Query โดยเลือกเมนู Data/ From Table/ Range

เข้ามาใน Power Query แล้ว

คลิกคอลัมน์ที่ต้องการปัดค่า (ในที่นี้คือคอลัมน์ Original) จากนั้นเลือกเมนูคำสั่ง Add Column/ Rounding/ Round …

จะมีกล่องข้อความถามว่า ต้องการ Round ทศนิยมตำแหน่งที่เท่าไร
ระบุเป็น 2 (เพื่อให้ตรงกับฟังก์ชัน ROUND ใน Excel) แล้วคลิก OK

ได้คอลัมน์ใหม่ที่ Round ทันที
ว้าว! ง่ายดายอะไรเบอร์นี้ ไม่ต้องเขียนสูตรซักตัว ^_^

มาลองเปรียบเทียบการ Round ใน Excel กับ Power Query กัน
ถ้าลองดูดี ๆ จะพบว่าผลลัพธ์บางค่าไม่เท่ากัน !

ถ้ามองให้ลึก จะพบว่าไม่เท่ากันเฉพาะกรณีทศนิยมตำแหน่งที่ 3 เป็นเลข 5

ถ้ามองให้ลึกกว่านั้น จะพบว่าไม่เท่ากันเฉพาะกรณีที่ทศนิยมตำแหน่งที่ 2 เป็นเลขคู่ !

ทำไมจึงเป็นเช่นนั้น?

เพราะค่าดีฟอลต์ของการ Round ใน Power Query คือ RoundingMode.ToEven

พูดแบบนี้อาจงง
ต้องอธิบายให้เห็นภาพโดยดูสูตรของ Power Query

Power Query มีสูตรด้วยเหรอ?

มีครับ
Power Query ถูกออกแบบให้ใช้ง่าย แค่คลิก ๆ ก็เสร็จ ไม่ต้องเขียนสูตร
แต่ความจริงแล้วทุกขั้นตอนมีสูตร หรือมีโค้ดแฝงอยู่ สูตรที่ว่าเขียนด้วยภาษา M (M Language, M Code)
ถ้าอยากเห็นสูตร ให้ไปที่เมนู View เลือก Formula Bar

จะแสดง Formula Bar ขึ้นมา และนั่นคือสูตรของขั้นตอนการสร้างคอลัมน์ Round

สูตรที่ว่าคือ

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

มีการใช้ฟังก์ชัน Number.Round ซึ่งเป็นฟังก์ชันของ M Code

Number.Round([Original], 2)

Number.Round คล้าย ROUND ใน Excel แต่ไม่เหมือนกัน

โครงสร้างของ Number.Round คือ

Number.Round( number, digits, [RoundingMode] )

number คือตัวเลขที่ต้องการปัดค่า เช่น เคสนี้คือคอลัมน์ [Original]
digits คือ ทศนิยมที่ต้องการ เช่น เคสนี้คือ 2 (กำหนดตอนใส่ค่าในกล่องที่ขึ้นมาถาม)
[RoundingMode] คือ โหมดของการ Round ซึ่งมีอยู่ 5 แบบคือ

1. RoundingMode.Up
2. RoundingMode.Down
3. RoundingMode.AwayFromZero
4. RoundingMode.TowardZero
5. RoundingMode.ToEven

[RoundingMode] เป็นออปชัน นั่นคือ ระบุหรือไม่ระบุก็ได้
ถ้าไม่ระบุ โปรแกรมจะใช้ค่าดีฟอลต์

ลองเดาไหมครับว่าค่าดีฟอลต์ของ RoundingMode คืออะไร?

เฉลย: ค่าดีฟอลต์คือ RoundingMode.ToEven

Even แปลว่า เลขคู่
RoundingMode.ToEven แปลง่าย ๆ ว่า ให้ปัดแบบปกติ แต่ถ้าตัวเลขเป็น 5 ให้ปัดแล้วได้ผลลัพธ์เป็นเลขคู่

งงใช่ไหมครับ มาดูตัวอย่างกัน

ต้องการปัด 3.685
เลขทศนิยมลำดับที่ 3 คือ 5
ถ้าปัดแบบปกติจะกลายเป็น 3.69
9 เป็นเลขคี่ ขัดกับ RoundingMode.ToEven จึงปัดลงเป็น 8
ผลลัพธ์ที่ได้คือ 3.68 !

ลองดูอีกเคสนึง 6.865
เลขทศนิยมลำดับที่ 3 คือ 5
ถ้าปัดแบบปกติจะกลายเป็น 6.87
7 เป็นเลขคี่ จึงปัดลงเป็น 6
ผลลัพธ์ที่ได้คือ 6.86 !

บ้ามากใช่ไหม?
ใช่ แต่คือเรื่องจริง และเป็นเรื่องที่เราไม่รู้

ทำไมถึงตั้งค่าดีฟอลต์ให้เป็นแบบนี้น่ะเหรอ?

เพื่อให้สอดคล้องกับมาตรฐาน IEEE หรือตรงกับข้อกำหนดที่เรียกว่า IEEE 754
มาตรฐานนี้มีความชัดเจนในกรณีการปัดตัวเลขเป็นบวกและเป็นลบ

ปัญหาคือคนส่วนใหญ่ไม่รู้ และส่วนใหญ่เข้าใจว่าถ้าเป็น 5 ให้ปัดขึ้น ผลลัพธ์ก็คือผิด!

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

ทางแก้คือ ให้ระบุ RoundingMode เป็น RoundingMode.AwayFromZero

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

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

ให้ใส่ , RoundingMode.AwayFromZero ต่อท้ายเลข 2 (ต้องสะกดแบบนี้เป๊ะ ๆ การใช้ M Code ต้องเป็น Case Sensitive)

= Table.AddColumn(#"Inserted Rounding", "Round", each Number.Round([Original], 2, RoundingMode.AwayFromZero), type number)

จะพบว่า Round ใน Power Query ตรงกับใน Excel และตรงกับที่เราอยากให้เป็น

พูดง่าย ๆ คือ การ Round ใน Power Query ต้องใส่ RoundingMode.AwayFromZero ทุกครั้ง !
(ยกเว้นต้องการ Round ให้ตรงกับมาตรฐาน IEEE 754)

การใช้ Round ใน Power BI ก็เหมือนกัน
ถ้าใช้ Power Query ใน Power BI Desktop ก็ต้องใส่ RoundingMode.AwayFromZero ต่อท้ายเช่นกัน

ดูยุ่งยากจัง ในอนาคตจะมีการแก้ไขค่าดีฟอลต์ไหม?

โดยส่วนตัวคิดว่ายาก เพราะกำหนดแบบนี้มาตั้งแต่เริ่ม และใช้มาหลายปีแล้ว หลายคนเข้าใจว่าค่าดีฟอลต์ของ Number.Round คือ RoundingMode.ToEven และใช้ค่านั้นไปแล้ว
ถ้าเปลี่ยนค่าดีฟอลต์ สิ่งที่เคยทำจะผิดทั้งหมด ซึ่งถือเป็นเรื่องใหญ่ ไมโครซอฟท์น่าจะปล่อยเลยตามเลย
ดังนั้น การใช้ Round ใน Power Query เป็นสิ่งที่ต้องระวัง

อ้อ ! เมื่อกี๊ผมบอกว่า RoundingMode มี 5 แบบใช่ไหมครับ
ขอบอกว่า
RoundingMode.Up ไม่เหมือนฟังก์ชัน ROUNDUP ใน Excel
RoundingMode.Down ก็ไม่เหมือนฟังก์ชัน ROUNDDOWN ใน Excel เช่นกัน

อ้าว! แล้วจะ Round Up, Round Down ยังไงล่ะ?

เนื่องจากบทความนี้ยาว (มาก) แล้ว ขอเล่าต่อใน ep2 นะครับ ^_^

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

หมายเหตุ: เนื้อหาในบทความนี้ได้แรงบันดาลใจจากบทความของคุณ Ken Puls

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

Reference:
https://www.excelguru.ca/blog/2014/09/17/power-query-the-round-function/
https://en.wikipedia.org/wiki/Rounding