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

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

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

หมายเหตุ

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

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

ตารางนี้จากสมุดงาน Excel มีข้อผิดพลาด Excel เช่น #NULL!, #REF!, และ #DIV/0! ใน คอลัมน์ อัตรา มาตรฐาน เมื่อคุณนําเข้าตารางนี้ลงในตัวแก้ไข 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 นิพจน์จัดการกับข้อผิดพลาดหรือไม่ รวมถึงค่าที่เหมาะสมหรือเรกคอร์ดข้อผิดพลาด

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

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

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

สําหรับการตรวจสอบเพิ่มเติม คุณสามารถขยาย คอลัมน์ 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

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

• ทําความเข้าใจและทํางานกับข้อผิดพลาดใน Power Query
• เพิ่มคอลัมน์แบบกําหนดเองใน Power Query
• เพิ่มคอลัมน์แบบมีเงื่อนไขใน Power Query