การใช้ฟังก์ชันแบบกําหนดเอง

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

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

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

สำคัญ

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

สร้างฟังก์ชันแบบกําหนดเองจากการอ้างอิงตาราง

หมายเหตุ

ตัวอย่างต่อไปนี้ถูกสร้างขึ้นโดยใช้ประสบการณ์ใช้งานเดสก์ท็อปที่พบใน Power BI Desktop และยังสามารถติดตามได้โดยใช้ประสบการณ์การใช้งาน Power Query ที่พบใน Excel สําหรับ Windows

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

เริ่มต้นโดยใช้ประสบการณ์การใช้งานตัวเชื่อมต่อโฟลเดอร์เพื่อนําทางไปยังโฟลเดอร์ที่ไฟล์ของคุณอยู่ และเลือกแปลงข้อมูลหรือแก้ไข ขั้นตอนเหล่านี้จะนําคุณไปยังประสบการณ์การใช้งาน Power Query คลิกขวาบนค่าไบนารีที่คุณเลือกจากเขตข้อมูลเนื้อหาและเลือกตัวเลือกเพิ่มเป็นคิวรีใหม่ สําหรับตัวอย่างนี้ การเลือกถูกสร้างขึ้นสําหรับไฟล์แรกจากรายการ ซึ่งเกิดขึ้นเป็นไฟล์เมษายน 2019.csv

ตัวเลือกนี้จะสร้างคิวรีใหม่ที่มีขั้นตอนการนําทางไปยังไฟล์นั้นโดยตรงเป็นไบนารีและชื่อของคิวรีใหม่นี้คือเส้นทางไฟล์ของไฟล์ที่เลือก เปลี่ยนชื่อคิวรี นี้เป็นไฟล์ตัวอย่าง

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

หมายเหตุ

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

ฟังก์ชันแบบกําหนดเองสามารถสร้างขึ้นโดยใช้ชนิดพารามิเตอร์ต่าง ๆ ไม่มีข้อกําหนดสําหรับฟังก์ชันแบบกําหนดเองใดๆ เพื่อให้มีไบนารีเป็นพารามิเตอร์

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

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

คลิกขวาที่ พารามิเตอร์ไฟล์ จากบานหน้าต่าง คิวรี เลือกตัวเลือก การอ้างอิง

เปลี่ยนชื่อคิวรีที่สร้างขึ้นใหม่จาก พารามิเตอร์ไฟล์ (2) เป็น ไฟล์ตัวอย่างการแปลงไฟล์ตัวอย่าง

คลิกขวาที่คิวรีไฟล์ตัวอย่างการแปลงใหม่นี้ และเลือกตัวเลือกสร้างฟังก์ชัน

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

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

• พารามิเตอร์ทั้งหมดที่ถูกอ้างอิงในคิวรีไฟล์ตัวอย่างการแปลงของคุณ
• คิวรีไฟล์ตัวอย่างการแปลงของคุณ ซึ่งมักเรียกว่าคิวรีตัวอย่าง
• ฟังก์ชันที่สร้างขึ้นใหม่ของคุณ ในกรณีนี้ คือ ไฟล์แปลง

ใช้การแปลงข้อมูลกับคิวรีตัวอย่าง

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

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

รูปแบบของไฟล์ CSV ทั้งหมดในโฟลเดอร์เหมือนกัน พวกเขาทั้งหมดมีส่วนหัวที่ครอบคลุมสี่แถวแรกบนสุด ส่วนหัวของคอลัมน์จะอยู่ในแถว 5 และข้อมูลเริ่มต้นจากแถวที่ 6 ลงไปด้านล่าง ดังที่แสดงในภาพถัดไป

ชุดถัดไปของขั้นตอนการแปลงที่จําเป็นต้องนําไปใช้กับ ไฟล์ ตัวอย่างการแปลงไฟล์คือ:

1. ลบแถวบนสุดสี่แถว- การดําเนินการนี้จะเป็นการกําจัดแถวที่ถือว่าเป็นส่วนหนึ่งของส่วนหัวของไฟล์

   

   หมายเหตุ

   หากต้องการเรียนรู้เพิ่มเติมเกี่ยวกับวิธีการลบแถวหรือกรองตารางตามตําแหน่งแถว ให้ไปที่ กรองตามตําแหน่งแถว

2. เลื่อนระดับส่วนหัว — ตอนนี้ส่วนหัวสําหรับตารางสุดท้ายของคุณอยู่ในแถวแรกของตาราง คุณสามารถเลื่อนระดับคอลัมน์ได้ดังที่แสดงในรูปถัดไป

   

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

หมายเหตุ

หากต้องการเรียนรู้เพิ่มเติมเกี่ยวกับวิธีการเลื่อนระดับและลดระดับส่วนหัว ให้ไปที่ เลื่อนระดับหรือลดระดับส่วนหัวของคอลัมน์

ข้อควรระวัง

ฟังก์ชันแปลงไฟล์ของคุณขึ้นอยู่กับขั้นตอนที่ดําเนินการในคิวรีไฟล์ตัวอย่างการแปลง อย่างไรก็ตาม ถ้าคุณพยายามปรับเปลี่ยนรหัสสําหรับ ฟังก์ชันแปลงไฟล์ ด้วยตนเอง คุณจะได้รับคําทักทายจากคําเตือนที่อ่าน The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

เรียกใช้ฟังก์ชันแบบกําหนดเองเป็นคอลัมน์ใหม่

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

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

หมายเหตุ

หากต้องการเรียนรู้เพิ่มเติมเกี่ยวกับวิธีการเลือกหรือลบคอลัมน์ออกจากตาราง ให้ไปที่ เลือกหรือลบคอลัมน์ออก

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

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

หมายเหตุ

สิ่งที่คุณอ่านจนถึงตอนนี้เป็นกระบวนการเดียวกับที่เกิดขึ้นระหว่างประสบการณ์การรวม ไฟล์ แต่ทําด้วยตนเอง

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

เพิ่มพารามิเตอร์ใหม่ลงในฟังก์ชันแบบกําหนดเองที่มีอยู่

สมมติว่ามีข้อกําหนดใหม่เกี่ยวกับสิ่งที่คุณสร้างขึ้นในขณะนี้ ข้อกําหนดใหม่ต้องการให้คุณรวมไฟล์ คุณกรองข้อมูลภายในเพื่อรับเฉพาะแถวที่ประเทศเท่ากับปานามา

เมื่อต้องการทําให้ข้อกําหนดนี้เกิดขึ้น ให้สร้างพารามิเตอร์ใหม่ที่เรียกว่า Market ด้วยชนิดข้อมูลข้อความ สําหรับค่าปัจจุบัน ให้ใส่ค่าปานามา

ด้วยพารามิเตอร์ใหม่นี้ เลือกคิวรีไฟล์ตัวอย่างแปลงและกรองเขตข้อมูลประเทศโดยใช้ค่าจากพารามิเตอร์ Market

หมายเหตุ

หากต้องการเรียนรู้เพิ่มเติมเกี่ยวกับวิธีการกรองคอลัมน์ตามค่า ให้ไปที่ ค่าตัวกรอง

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

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

หากต้องการแก้ไขข้อผิดพลาด ให้ดับเบิลคลิก ที่ฟังก์ชัน แบบกําหนดเองที่เรียกใช้ใน ขั้นตอนที่ ใช้ เพื่อเปิด หน้าต่าง เรียกฟังก์ชัน แบบกําหนดเอง ใน พารามิเตอร์ Market ให้ป้อนค่า Panama ด้วยตนเอง

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

สร้างฟังก์ชันแบบกําหนดเองจากตรรกะที่สามารถนํามาใช้ใหม่ได้

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

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

รหัส
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

คุณเริ่มต้นด้วยการมีพารามิเตอร์ที่มีค่าที่ทําหน้าที่เป็นตัวอย่าง สําหรับกรณีนี้ นี่คือค่า PTY-CM1090-LAX

จากพารามิเตอร์นั้น คุณสร้างคิวรีใหม่ที่คุณนําการแปลงที่คุณต้องการไปใช้ สําหรับกรณีนี้ คุณต้องการแยกรหัส PTY-CM1090-LAX ออกเป็นหลายคอมโพเนนต์:

• จุดเริ่มต้น = PTY
• ปลายทาง = LAX
• Airline = CM
• FlightID = 1090

รหัส M ต่อไปนี้แสดงให้เห็นว่าชุดของการแปลงนั้น

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

หมายเหตุ

เมื่อต้องการเรียนรู้เพิ่มเติมเกี่ยวกับภาษาสูตร Power Query M ไปที่ ภาษาสูตร Power Query M

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

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