หมายเหตุ
การเข้าถึงหน้านี้ต้องได้รับการอนุญาต คุณสามารถลอง ลงชื่อเข้าใช้หรือเปลี่ยนไดเรกทอรีได้
การเข้าถึงหน้านี้ต้องได้รับการอนุญาต คุณสามารถลองเปลี่ยนไดเรกทอรีได้
Power Query มีไวยากรณ์ของตนเองเพื่อทดสอบและตรวจจับข้อผิดพลาด ซึ่งคล้ายกับวิธีที่ Excel และภาษา DAX มี IFERROR ฟังก์ชัน
ตามที่กล่าวถึงในบทความเกี่ยวกับ การจัดการกับข้อผิดพลาดใน Power Query ข้อผิดพลาดอาจปรากฏขึ้นในระดับขั้นตอนหรือระดับเซลล์ บทความนี้มุ่งเน้นไปที่วิธีที่คุณสามารถจับและจัดการข้อผิดพลาดตามตรรกะเฉพาะของคุณเอง
Note
เพื่อสาธิตแนวคิดนี้ บทความนี้ใช้เวิร์กบุ๊ก Excel เป็นแหล่งข้อมูล แนวคิดที่แสดงที่นี่นําไปใช้กับค่าทั้งหมดใน Power Query และไม่เพียงแค่แนวคิดที่มาจากเวิร์กบุ๊ก Excel เท่านั้น
แหล่งข้อมูลตัวอย่างสําหรับการสาธิตนี้คือ เวิร์กบุ๊ก Excel ที่มีตารางต่อไปนี้
ตารางนี้จากสมุดงาน Excel มีข้อผิดพลาดของ Excel เช่น #NULL!, #REF! และ #DIV/0! ในคอลัมน์ อัตรามาตรฐาน เมื่อคุณนําเข้าตารางนี้ลงในตัวแก้ไข Power Query รูปภาพต่อไปนี้แสดงลักษณะที่ปรากฏ
ให้สังเกตว่าข้อผิดพลาดจากเวิร์กบุ๊ก Excel จะแสดงด้วย [Error] ค่าในแต่ละเซลล์อย่างไร
ในบทความนี้ คุณจะได้เรียนรู้วิธีการแทนที่ข้อผิดพลาดด้วยค่าอื่น นอกจากนี้คุณยังได้เรียนรู้วิธีการจับข้อผิดพลาดและใช้สําหรับตรรกะเฉพาะของคุณเอง
ระบุค่าแสดงแทนเมื่อค้นหาข้อผิดพลาด
ในกรณีนี้ เป้าหมายคือการสร้างคอลัมน์ อัตราสุดท้าย ใหม่ในแหล่งข้อมูลตัวอย่างที่ใช้ค่าจากคอลัมน์ อัตรามาตรฐาน หากมีข้อผิดพลาดใดๆ จะใช้ค่าจากคอลัมน์ อัตราพิเศษ ที่สอดคล้องกัน
เมื่อต้องการสร้างคอลัมน์แบบกําหนดเองใหม่ ให้ไปที่เมนู เพิ่มคอลัมน์ แล้วเลือก คอลัมน์แบบกําหนดเอง ในหน้าต่าง คอลัมน์แบบกําหนดเอง ให้ป้อนสูตรtry [Standard Rate] otherwise [Special Rate] ตั้งชื่อคอลัมน์ใหม่นี้ว่า อัตราสุดท้าย
สูตรนี้พยายามประเมินคอลัมน์ อัตรามาตรฐาน และส่งออกค่าถ้าไม่พบข้อผิดพลาด หากพบข้อผิดพลาดในคอลัมน์อัตรามาตรฐาน ผลลัพธ์คือค่าที่กําหนดไว้หลังotherwiseคําสั่ง ซึ่งในกรณีนี้คือคอลัมน์อัตราพิเศษ
หลังจากที่คุณเพิ่มชนิดข้อมูลที่ถูกต้องไปยังคอลัมน์ทั้งหมดในตาราง รูปภาพต่อไปนี้แสดงลักษณะที่ปรากฏของตารางขั้นสุดท้าย
Note
อีกวิธีหนึ่ง คือ คุณยังสามารถป้อนสูตร try [Standard Rate] catch ()=> [Special Rate]ซึ่งเทียบเท่ากับสูตรก่อนหน้า แต่ใช้คําสําคัญ catch กับฟังก์ชันที่ไม่ต้องใช้พารามิเตอร์
คํา catch สําคัญถูกนํามาใช้ใน Power Query ในเดือนพฤษภาคม 2022
ระบุตรรกะข้อผิดพลาดตามเงื่อนไขของคุณเอง
การใช้แหล่งข้อมูลตัวอย่างเดียวกันกับส่วนก่อนหน้า เป้าหมายใหม่คือการสร้างคอลัมน์ใหม่สําหรับอัตราสุดท้าย หากมีค่าจาก อัตรามาตรฐาน ระบบจะใช้ค่านั้น มิฉะนั้น ค่าจากคอลัมน์ อัตราพิเศษ จะถูกใช้ ยกเว้นแถวที่มีข้อผิดพลาดใดๆ#REF!
Note
วัตถุประสงค์เพียงอย่างเดียวในการ #REF! ยกเว้นข้อผิดพลาดมีไว้เพื่อวัตถุประสงค์ในการสาธิต ด้วยแนวคิดที่แสดงในบทความนี้ คุณสามารถกําหนดเป้าหมายเขตข้อมูลที่คุณเลือกจากเรกคอร์ดข้อผิดพลาดได้
เมื่อคุณเลือกช่องว่างใด ๆ ที่อยู่ถัดจากค่าข้อผิดพลาด คุณจะได้รับบานหน้าต่างรายละเอียดที่ด้านล่างของหน้าจอ บานหน้าต่างรายละเอียดประกอบด้วยทั้งสาเหตุข้อผิดพลาด และ DataFormat.Errorข้อความ Invalid cell value '#REF!'แสดงข้อผิดพลาด :
คุณสามารถเลือกได้ครั้งละหนึ่งเซลล์เท่านั้น ดังนั้นคุณจึงสามารถตรวจสอบคอมโพเนนต์ของข้อผิดพลาดของค่าข้อผิดพลาดได้ครั้งละหนึ่งค่าเท่านั้น ซึ่งอยู่ที่จุดนี้คุณสร้างคอลัมน์แบบกําหนดเองใหม่และใช้ try นิพจน์
ใช้กับ try ตรรกะแบบกําหนดเอง
เมื่อต้องการสร้างคอลัมน์แบบกําหนดเองใหม่ ให้ไปที่เมนู เพิ่มคอลัมน์ แล้วเลือก คอลัมน์แบบกําหนดเอง ในหน้าต่าง คอลัมน์แบบกําหนดเอง ให้ป้อนสูตรtry [Standard Rate] ตั้งชื่อคอลัมน์ใหม่นี้ว่า ข้อผิดพลาดทั้งหมด
นิพจน์ try แปลงค่าและข้อผิดพลาด เป็นค่าเรกคอร์ดที่ระบุว่า try นิพจน์จัดการกับข้อผิดพลาดหรือไม่ และค่าที่เหมาะสมหรือเรกคอร์ดข้อผิดพลาด
คุณสามารถขยายคอลัมน์ที่สร้างขึ้นใหม่นี้พร้อมค่าเรกคอร์ด และดูเขตข้อมูลที่มีที่จะขยายโดยการเลือกไอคอนที่อยู่ถัดจากส่วนหัวของคอลัมน์
การดําเนินการนี้จะแสดงเขตข้อมูลใหม่สามเขต:
- All Errors.HasError—แสดงว่าค่าจากคอลัมน์ อัตรามาตรฐาน มีข้อผิดพลาดหรือไม่
-
ข้อผิดพลาดทั้งหมดค่า - หากค่าจากคอลัมน์อัตรามาตรฐานไม่มีข้อผิดพลาด คอลัมน์นี้จะแสดงค่าจากคอลัมน์อัตรามาตรฐาน สําหรับค่าที่มีข้อผิดพลาด เขตข้อมูลนี้ไม่พร้อมใช้งาน และในระหว่างการดําเนินการขยาย คอลัมน์นี้มีค่า
null -
ข้อผิดพลาดทั้งหมด—หากค่าจากคอลัมน์ อัตรามาตรฐาน มีข้อผิดพลาด คอลัมน์นี้จะแสดงเรกคอร์ดข้อผิดพลาดสําหรับค่าจากคอลัมน์ อัตรามาตรฐาน สําหรับค่าที่ไม่มีข้อผิดพลาด เขตข้อมูลนี้ไม่พร้อมใช้งาน และในระหว่างการดําเนินการขยาย คอลัมน์นี้มีค่า
null
สําหรับการตรวจสอบเพิ่มเติม คุณสามารถขยายคอลัมน์ All Errors.Error เพื่อรับองค์ประกอบสามประการของเรกคอร์ดข้อผิดพลาด:
- สาเหตุของข้อผิดพลาด
- ข้อความแสดงข้อผิดพลาด
- รายละเอียดข้อผิดพลาด
หลังจากดําเนินการขยาย ฟิลด์ All Errors.Error.Message จะแสดงข้อความแสดงข้อผิดพลาดเฉพาะที่บอกคุณอย่างชัดเจนว่าแต่ละเซลล์มีข้อผิดพลาด Excel อะไรบ้าง ข้อความแสดงข้อผิดพลาดได้มาจากฟิลด์ ข้อความแสดงข้อผิดพลาด ของเรกคอร์ดข้อผิดพลาด
ตอนนี้ด้วยข้อความแสดงข้อผิดพลาดแต่ละข้อความในคอลัมน์ใหม่คุณสามารถสร้างคอลัมน์แบบมีเงื่อนไขใหม่ด้วยชื่อ อัตราสุดท้าย และคําสั่งต่อไปนี้:
- ถ้าค่าในคอลัมน์ All Errors.Error.Message เท่ากับ
nullผลลัพธ์จะเป็นค่าจากคอลัมน์ อัตรามาตรฐาน - มิฉะนั้น ถ้าค่าในคอลัมน์ All Errors.Error.Message ไม่เท่ากับ
Invalid cell value '#REF!'.ผลลัพธ์จะเป็นค่าจากคอลัมน์ อัตราพิเศษ - มิฉะนั้น เป็นโมฆะ
หลังจากเก็บเฉพาะคอลัมน์ บัญชีอัตรามาตรฐานอัตราพิเศษ และ อัตรา สุดท้าย และเพิ่มชนิดข้อมูลที่ถูกต้องสําหรับแต่ละคอลัมน์ รูปภาพต่อไปนี้แสดงให้เห็นว่าตารางสุดท้ายมีลักษณะอย่างไร
ใช้ try และ catch ด้วยตรรกะแบบกําหนดเอง
อีกวิธีหนึ่งคือ คุณยังสามารถสร้างคอลัมน์แบบกําหนดเองใหม่โดยใช้ try คําสําคัญ และ catch ได้
try [Standard Rate] catch (r)=> if r[Message] <> "Invalid cell value '#REF!'." then [Special Rate] else null
