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

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

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

หมายเหตุ

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

การใช้ตรรกะตามเงื่อนไขที่ยึดตามข้อผิดพลาด

แหล่งข้อมูลตัวอย่างในการสาธิตนี้เป็นExcelสมุดงานที่มีตารางต่อไปนี้:

ข้อมูลตัวอย่างจากExcelของคุณ

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

ตารางตัวอย่างPower Queryตาราง

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

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

เพิ่มคอลัมน์แบบปรับแต่งเอง try ด้วย และ otherwise ไวยากรณ์

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

หรือลองคอลัมน์แบบปรับแต่งเอง

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

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

ตารางสุดท้ายลองเป็นอย่างอื่น

การจับข้อผิดพลาดและใช้ตรรกะ try แบบมีเงื่อนไขแบบปรับแต่งเอง

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

หมายเหตุ

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

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

ข้อผิดพลาดตัวอย่าง

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

เพิ่มคอลัมน์แบบเองด้วย try ไวยากรณ์

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

ใช้การลองใช้ในคอลัมน์แบบปรับแต่งเอง

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

ลองใช้ค่าระเบียน

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

ขยายค่าเรกคอร์ด

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

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

ลองเขตข้อมูลที่ขยาย

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

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

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

ข้อความแสดงข้อผิดพลาดที่ระบุ

เพิ่มคอลัมน์แบบมีเงื่อนไข

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

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

คอลัมน์แบบมีเงื่อนไขจากข้อความแสดงข้อผิดพลาด

หลังจากเก็บเฉพาะ คอลัมน์ บัญชี , อัตรา มาตรฐาน, อัตราพิเศษ และ อัตราสุดท้าย และเพิ่มชนิดข้อมูลที่ถูกต้องของแต่ละคอลัมน์ รูปต่อไปนี้จะมีลักษณะเป็นตารางสุดท้าย

ตารางสุดท้ายที่มีชนิดข้อมูล

ทรัพยากรเพิ่มเติม