การใช้ฟังก์ชันแบบกําหนดเอง
บทความ 03/05/2567
6 ผู้สนับสนุน
คำติชม
ในบทความนี้
สร้างฟังก์ชันแบบกําหนดเองจากการอ้างอิงตาราง
สร้างฟังก์ชันแบบกําหนดเองจากตรรกะที่สามารถนํามาใช้ใหม่ได้
ถ้าคุณพบว่าตัวเองอยู่ในสถานการณ์ที่คุณจําเป็นต้องใช้การแปลงชุดเดียวกันกับคิวรีหรือค่าอื่น ๆ การสร้างฟังก์ชันแบบกําหนดเองของ 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 ลงไปด้านล่าง ดังที่แสดงในภาพถัดไป
ชุดถัดไปของขั้นตอนการแปลงที่จําเป็นต้องนําไปใช้กับ ไฟล์ ตัวอย่างการแปลงไฟล์คือ:
ลบแถวบนสุดสี่แถว - การดําเนินการนี้จะเป็นการกําจัดแถวที่ถือว่าเป็นส่วนหนึ่งของส่วนหัวของไฟล์
หมายเหตุ
หากต้องการเรียนรู้เพิ่มเติมเกี่ยวกับวิธีการลบแถวหรือกรองตารางตามตําแหน่งแถว ให้ไปที่ กรองตามตําแหน่ง แถว
เลื่อนระดับส่วนหัว — ตอนนี้ส่วนหัวสําหรับตารางสุดท้ายของคุณอยู่ในแถวแรกของตาราง คุณสามารถเลื่อนระดับคอลัมน์ได้ดังที่แสดงในรูปถัดไป
หลังจากเลื่อนระดับส่วนหัวของคอลัมน์ตามค่าเริ่มต้นแล้ว 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
จากนั้น คุณสามารถแปลงคิวรีนั้นให้เป็นฟังก์ชัน โดยการคลิกขวาบนคิวรี และเลือก สร้างฟังก์ชัน ในตอนท้าย คุณสามารถเรียกใช้ฟังก์ชันแบบกําหนดเองของคุณลงในคิวรีหรือค่าใด ๆ ของคุณ ดังที่แสดงในรูปภาพถัดไป
หลังจากการแปลงเพิ่มเติมเล็กน้อย คุณจะเห็นว่าคุณได้ผลลัพธ์ที่คุณต้องการและใช้ตรรกะสําหรับการแปลงดังกล่าวจากฟังก์ชันแบบกําหนดเอง