อ่านในภาษาอังกฤษ

แชร์ผ่าน


การจัดการข้อผิดพลาด

Power Query มีไวยากรณ์ของตนเองเพื่อทดสอบและตรวจจับข้อผิดพลาด ซึ่งคล้ายกับวิธีที่ Excel และภาษา DAX มี IFERROR ฟังก์ชัน

ตามที่กล่าวถึงในบทความเกี่ยวกับ การจัดการกับข้อผิดพลาดใน Power Query ข้อผิดพลาดอาจปรากฏขึ้นในระดับขั้นตอนหรือระดับเซลล์ บทความนี้มุ่งเน้นไปที่วิธีที่คุณสามารถจับและจัดการข้อผิดพลาดตามตรรกะเฉพาะของคุณเอง

หมายเหตุ

เพื่อสาธิตแนวคิดนี้ บทความนี้ใช้เวิร์กบุ๊ก Excel เป็นแหล่งข้อมูล แนวคิดที่แสดงที่นี่นําไปใช้กับค่าทั้งหมดใน Power Query และไม่เพียงแค่แนวคิดที่มาจากเวิร์กบุ๊ก Excel เท่านั้น

แหล่งข้อมูลตัวอย่างสําหรับการสาธิตนี้คือ เวิร์กบุ๊ก Excel ที่มีตารางต่อไปนี้

สกรีนช็อตของข้อมูลตัวอย่างจากเวิร์กบุ๊ก Excel ที่มีข้อผิดพลาดสามอย่าง

ตารางนี้จากสมุดงาน Excel มีข้อผิดพลาด Excel เช่น #NULL!, #REF!, และ #DIV/0! ใน คอลัมน์ อัตรา มาตรฐาน เมื่อคุณนําเข้าตารางนี้ลงในตัวแก้ไข Power Query รูปภาพต่อไปนี้แสดงลักษณะที่ปรากฏ

สกรีนช็อตของตารางตัวอย่างที่โหลดใน Power Query

ให้สังเกตว่าข้อผิดพลาดจากเวิร์กบุ๊ก Excel จะแสดงด้วย [Error] ค่าในแต่ละเซลล์อย่างไร

ในบทความนี้ คุณจะได้เรียนรู้วิธีการแทนที่ข้อผิดพลาดด้วยค่าอื่น นอกจากนี้คุณยังได้เรียนรู้วิธีการจับข้อผิดพลาดและใช้สําหรับตรรกะเฉพาะของคุณเอง

ระบุค่าแสดงแทนเมื่อค้นหาข้อผิดพลาด

ในกรณีนี้ เป้าหมายคือการสร้างคอลัมน์อัตราสุดท้ายใหม่ในแหล่งข้อมูลตัวอย่างที่ใช้ค่าจากคอลัมน์อัตรามาตรฐาน ถ้ามีข้อผิดพลาดใด ๆ ระบบจะใช้ค่าจากคอลัมน์อัตราพิเศษที่สอดคล้องกัน

หากต้องการสร้างคอลัมน์แบบกําหนดเองใหม่ ให้ไปที่ เมนู เพิ่มคอลัมน์ และเลือก คอลัมน์แบบกําหนดเอง ในหน้าต่าง คอลัมน์แบบกําหนดเอง ให้ใส่สูตรtry [Standard Rate] otherwise [Special Rate] ตั้งชื่อคอลัมน์ ใหม่นี้อัตราสุดท้าย

สกรีนช็อตที่มีกล่องโต้ตอบคอลัมน์แบบกําหนดเองเปิดขึ้นและลองสูตรที่ป้อนในคอลัมน์แบบกําหนดเอง

สูตรนี้พยายามประเมิน คอลัมน์อัตรา มาตรฐานและแสดงค่าหากไม่พบข้อผิดพลาด ถ้าพบข้อผิดพลาดใน คอลัมน์อัตรา มาตรฐาน ผลลัพธ์คือค่าที่กําหนดไว้หลังจาก otherwise คําสั่ง ซึ่งในกรณีนี้คือ คอลัมน์อัตรา พิเศษ

หลังจากที่คุณเพิ่มชนิดข้อมูลที่ถูกต้องไปยังคอลัมน์ทั้งหมดในตาราง รูปภาพต่อไปนี้แสดงลักษณะที่ปรากฏของตารางขั้นสุดท้าย

สกรีนช็อตของตารางที่มีข้อผิดพลาดในอัตรามาตรฐานถูกแทนที่ด้วยอัตราพิเศษในคอลัมน์อัตราสุดท้าย

หมายเหตุ

อีกวิธีหนึ่ง คือ คุณยังสามารถป้อนสูตร try [Standard Rate] catch ()=> [Special Rate]ซึ่งเทียบเท่ากับสูตรก่อนหน้า แต่ใช้คําสําคัญ catch กับฟังก์ชันที่ไม่ต้องใช้พารามิเตอร์

คํา catch สําคัญถูกนํามาใช้ใน Power Query ในเดือนพฤษภาคม 2022

ระบุตรรกะข้อผิดพลาดตามเงื่อนไขของคุณเอง

เป้าหมายใหม่คือการสร้างคอลัมน์ใหม่สําหรับ อัตราสุดท้ายโดยใช้แหล่งข้อมูลตัวอย่างเดียวกันกับส่วนที่แล้ว หากค่าจาก อัตรา มาตรฐานอยู่ ค่าดังกล่าวจะถูกใช้ มิฉะนั้นจะใช้ค่าจาก คอลัมน์อัตรา พิเศษ ยกเว้นแถวที่ #REF! มีข้อผิดพลาด

หมายเหตุ

วัตถุประสงค์เพียงอย่างเดียวในการ #REF! ยกเว้นข้อผิดพลาดมีไว้เพื่อวัตถุประสงค์ในการสาธิต ด้วยแนวคิดที่แสดงในบทความนี้ คุณสามารถกําหนดเป้าหมายเขตข้อมูลที่คุณเลือกจากเรกคอร์ดข้อผิดพลาดได้

เมื่อคุณเลือกช่องว่างใด ๆ ที่อยู่ถัดจากค่าข้อผิดพลาด คุณจะได้รับบานหน้าต่างรายละเอียดที่ด้านล่างของหน้าจอ บานหน้าต่างรายละเอียดประกอบด้วยทั้งสาเหตุข้อผิดพลาด และ DataFormat.Errorข้อความ Invalid cell value '#REF!'แสดงข้อผิดพลาด :

สกรีนช็อตของข้อผิดพลาดที่เลือก พร้อมข้อความแสดงข้อผิดพลาดที่ด้านล่างของกล่องโต้ตอบ

คุณสามารถเลือกได้ครั้งละหนึ่งเซลล์เท่านั้น ดังนั้นคุณจึงสามารถตรวจสอบคอมโพเนนต์ของข้อผิดพลาดของค่าข้อผิดพลาดได้ครั้งละหนึ่งค่าเท่านั้น ซึ่งอยู่ที่จุดนี้คุณสร้างคอลัมน์แบบกําหนดเองใหม่และใช้ try นิพจน์

ใช้กับ try ตรรกะแบบกําหนดเอง

หากต้องการสร้างคอลัมน์แบบกําหนดเองใหม่ ให้ไปที่ เมนู เพิ่มคอลัมน์ และเลือก คอลัมน์แบบกําหนดเอง ในหน้าต่าง คอลัมน์แบบกําหนดเอง ให้ใส่สูตรtry [Standard Rate] ตั้งชื่อคอลัมน์ ใหม่ว่า ข้อผิดพลาดทั้งหมด

สกรีนช็อตที่มีกล่องโต้ตอบคอลัมน์แบบกําหนดเองเปิดขึ้นและลองสูตรที่ป้อนในคอลัมน์แบบกําหนดเอง

นิพจน์ try แปลงค่าและข้อผิดพลาด เป็นค่าเรกคอร์ดที่ระบุว่า try นิพจน์จัดการกับข้อผิดพลาดหรือไม่ และค่าที่เหมาะสมหรือเรกคอร์ดข้อผิดพลาด

สกรีนช็อตของตารางที่มีการเพิ่มคอลัมน์ข้อผิดพลาดทั้งหมดและข้อผิดพลาดที่แสดงเป็นระเบียน

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

สกรีนช็อตของคอลัมน์ข้อผิดพลาดทั้งหมดที่มีไอคอนขยายที่เน้นและกล่อง HasError, Value และข้อผิดพลาดที่เลือก

การดําเนินการนี้จะแสดงเขตข้อมูลใหม่สามเขต:

  • Errors.HasError ทั้งหมด—แสดงว่าค่าจาก คอลัมน์อัตรา มาตรฐานมีข้อผิดพลาดหรือไม่
  • Errors.Value ทั้งหมดถ้าค่าจาก คอลัมน์อัตรา มาตรฐานไม่มีข้อผิดพลาด คอลัมน์นี้จะแสดงค่าจาก คอลัมน์อัตรา มาตรฐาน สําหรับค่าที่มีข้อผิดพลาด เขตข้อมูลนี้ไม่พร้อมใช้งาน และในระหว่างการดําเนินการขยาย คอลัมน์นี้มีค่าnull
  • Errors.Error ทั้งหมด—ถ้าค่าจาก คอลัมน์อัตรา มาตรฐานมีข้อผิดพลาด คอลัมน์นี้แสดงระเบียนข้อผิดพลาดสําหรับค่าจาก คอลัมน์อัตรา มาตรฐาน สําหรับค่าที่ไม่มีข้อผิดพลาด เขตข้อมูลนี้ไม่พร้อมใช้งาน และในระหว่างการดําเนินการขยาย คอลัมน์นี้มีค่าnull

ภาพหน้าจอของตารางที่มีเขตข้อมูลใหม่ในคอลัมน์ที่มีค่า All.Errors.Error หนึ่งค่าที่เลือกไว้ และแสดงข้อความข้อผิดพลาดด้านล่างตาราง

สําหรับการตรวจสอบเพิ่มเติม คุณสามารถขยาย คอลัมน์ Errors.Error ทั้งหมดเพื่อรับคอมโพเนนต์สามรายการของระเบียนข้อผิดพลาด:

  • เหตุผลของข้อผิดพลาด
  • ข้อความแสดงข้อผิดพลาด
  • รายละเอียดข้อผิดพลาด

หลังจากทําการดําเนินการ ขยาย เขตข้อมูล Errors.Error.Message ทั้งหมด จะแสดงข้อความแสดงข้อผิดพลาดเฉพาะที่บอกให้คุณทราบว่าแต่ละเซลล์มีข้อผิดพลาดของ Excel อะไรบ้าง ข้อความแสดงข้อผิดพลาดมาจาก เขตข้อมูล ข้อความ แสดงข้อผิดพลาด ของระเบียนข้อผิดพลาด

สกรีนช็อตที่มีข้อความแสดงข้อผิดพลาดเฉพาะปรากฏขึ้น

ตอนนี้ ด้วยข้อผิดพลาดแต่ละข้อความในคอลัมน์ใหม่ คุณสามารถสร้างคอลัมน์แบบมีเงื่อนไขใหม่ด้วยชื่อ อัตรา สุดท้าย และส่วนคําสั่งต่อไปนี้:

  • ถ้าค่าใน คอลัมน์ All Errors.Errors.Message เท่ากับ nullผลลัพธ์จะเป็นค่าจาก คอลัมน์ อัตรา มาตรฐาน
  • มิฉะนั้น หากค่าใน คอลัมน์ All Errors.Errors.Message ไม่เท่ากัน Invalid cell value '#REF!'.ผลลัพธ์จะเป็นค่าจาก คอลัมน์อัตรา พิเศษ
  • มิฉะนั้น เป็น null

ภาพหน้าจอของกล่องโต้ตอบเพิ่มคอลัมน์แบบมีเงื่อนไขที่มีเงื่อนไขข้อผิดพลาดทั้งหมดที่ตั้งค่าไว้สําหรับคอลัมน์ใหม่

หลังจากเก็บเฉพาะคอลัมน์ Account, Rate, Standard Rate, Special Rate และ Final Rate และเพิ่มชนิดข้อมูลที่ถูกต้องสําหรับแต่ละคอลัมน์แล้ว รูปภาพต่อไปนี้จะแสดงให้เห็นว่าตารางสุดท้ายมีลักษณะเป็นอย่างไร

สกรีนช็อตของตารางขั้นสุดท้ายที่มีคอลัมน์ที่เหลือซึ่งมีชนิดข้อมูลที่เหมาะสม

ใช้ try และ catch ด้วยตรรกะแบบกําหนดเอง

อีกวิธีหนึ่งคือ คุณยังสามารถสร้างคอลัมน์แบบกําหนดเองใหม่โดยใช้ try คําสําคัญ และ catch ได้

try [Standard Rate] catch (r)=> if r[Message] <> "Invalid cell value '#REF!'." then [Special Rate] else null

กล่องโต้ตอบคอลัมน์แบบกําหนดเองที่มีสูตรใหม่แสดงวิธีลองและตรวจจับไวยากรณ์

แหล่งข้อมูลเพิ่มเติม