การใช้ฟังก์ชันแบบกําหนดเอง
ถ้าคุณพบว่าตัวเองอยู่ในสถานการณ์ที่คุณจําเป็นต้องใช้การแปลงชุดเดียวกันกับคิวรีหรือค่าอื่น ๆ การสร้างฟังก์ชันแบบกําหนดเองของ Power Query ที่สามารถนํามาใช้ใหม่ได้หลายครั้งตามที่คุณต้องการจะเป็นประโยชน์ ฟังก์ชันแบบกําหนดเองของ Power Query คือการแมปจากชุดของค่าอินพุตไปยังค่าเอาต์พุตเดียว และถูกสร้างขึ้นจากฟังก์ชัน M ดั้งเดิมและตัวดําเนินการ
คุณสามารถสร้างฟังก์ชันแบบกําหนดเองของ Power Query ด้วยตนเองโดยใช้ภาษาสูตร Power Query Mหรือส่วนติดต่อผู้ใช้ Power Query เสนอคุณลักษณะเพื่อเร่งความเร็ว ลดความซับซ้อน และปรับปรุงกระบวนการสร้างและจัดการฟังก์ชันแบบกําหนดเอง
ก่อนอื่นเราจะครอบคลุมขั้นตอนพื้นฐานสําหรับ สร้างฟังก์ชันแบบกําหนดเองด้วยรหัสในUI จากนั้นเราจะมุ่งเน้นไปที่การใช้อินเทอร์เฟซเพื่อ เปลี่ยนการดําเนินการที่ซับซ้อนให้เป็นฟังก์ชันที่นํากลับมาใช้ใหม่ได้
สําคัญ
บทความนี้สรุปวิธีการสร้างฟังก์ชันแบบกําหนดเองด้วย Power Query โดยใช้การแปลงทั่วไปที่สามารถเข้าถึงได้ในอินเทอร์เฟซผู้ใช้ Power Query ซึ่งมุ่งเน้นแนวคิดหลักในการสร้างฟังก์ชันแบบกําหนดเอง และลิงก์ไปยังบทความอื่น ๆ ในเอกสาร Power Query สําหรับข้อมูลเพิ่มเติมเกี่ยวกับการแปลงเฉพาะที่อ้างอิงในบทความนี้
สร้างฟังก์ชันแบบกําหนดเองจากรหัสใน UI
โน้ต
คุณสามารถทําตามขั้นตอนต่อไปนี้ใน Power BI Desktop หรือใช้ประสบการณ์การใช้งาน Power Query ที่พบใน Excel สําหรับ Windows
- ใช้ประสบการณ์การใช้งานตัวเชื่อมต่อเพื่อเชื่อมต่อกับข้อมูลของคุณที่อยู่ของตัวเชื่อมต่อ เมื่อคุณเลือกข้อมูลของคุณแล้ว ให้เลือกปุ่ม แปลงข้อมูล หรือ แก้ไข ซึ่งจะนําคุณไปยังประสบการณ์การใช้งาน Power Query
- คลิกขวาที่พื้นที่ว่างในบานหน้าต่าง คิวรี่ ทางด้านซ้าย
- เลือก คิวรีเปล่า
- ในหน้าต่างคิวรีที่ว่างเปล่าใหม่ ให้เลือกเมนู หน้าแรก ของ จากนั้น ตัวแก้ไขขั้นสูง
- แทนที่เทมเพลตด้วยฟังก์ชันแบบกําหนดเองของคุณ เช่น:
let HelloWorld = () => ("Hello World") in HelloWorld
- เลือก เสร็จสิ้น
สําหรับข้อมูลเพิ่มเติมเกี่ยวกับการพัฒนาฟังก์ชันแบบกําหนดเองด้วยภาษาสูตร Power Query M โปรดดูที่บทความนี้: ทําความเข้าใจฟังก์ชัน Power Query M ในส่วนต่อไปนี้ มีบทช่วยสอนที่อธิบายถึงวิธีที่คุณสามารถใช้ส่วนติดต่อผู้ใช้ Power Query เพื่อพัฒนาฟังก์ชันแบบกําหนดเองโดยไม่ต้องเขียนโค้ด และคําแนะนําเกี่ยวกับวิธีที่คุณสามารถเรียกใช้ฟังก์ชันแบบกําหนดเองของคุณในคิวรีของคุณ
สร้างฟังก์ชันแบบกําหนดเองจากบทช่วยสอนการอ้างอิงตาราง
โน้ต
ตัวอย่างต่อไปนี้ถูกสร้างขึ้นโดยใช้ประสบการณ์ใช้งานเดสก์ท็อปที่พบใน Power BI Desktop และยังสามารถติดตามได้โดยใช้ประสบการณ์การใช้งาน Power Query ที่พบใน Excel สําหรับ Windows
คุณสามารถทําตามตัวอย่างนี้ โดยการดาวน์โหลดไฟล์ตัวอย่างที่ใช้ในบทความนี้จากลิงก์ดาวน์โหลด ต่อไปนี้ เพื่อความง่าย บทความนี้ใช้ตัวเชื่อมต่อโฟลเดอร์ เมื่อต้องการเรียนรู้เพิ่มเติมเกี่ยวกับตัวเชื่อมต่อโฟลเดอร์ ไปที่ โฟลเดอร์ เป้าหมายของตัวอย่างนี้คือการสร้างฟังก์ชันแบบกําหนดเองที่สามารถนําไปใช้กับไฟล์ทั้งหมดในโฟลเดอร์นั้นก่อนที่จะรวมข้อมูลทั้งหมดจากไฟล์ทั้งหมดลงในตารางเดียว
เริ่มต้นโดยใช้ประสบการณ์การใช้งานตัวเชื่อมต่อโฟลเดอร์เพื่อนําทางไปยังโฟลเดอร์ที่ไฟล์ของคุณอยู่ และเลือก แปลงข้อมูล หรือ แก้ไข ขั้นตอนเหล่านี้จะนําคุณไปยังประสบการณ์การใช้งาน Power Query คลิกขวาบนค่า ไบนารี ที่คุณเลือกจากเขตข้อมูลเนื้อหา และเลือกตัวเลือก เพิ่มเป็น คิวรีใหม่ สําหรับตัวอย่างนี้ การเลือกถูกสร้างขึ้นสําหรับไฟล์แรกจากรายการ ซึ่งเกิดขึ้นเป็นไฟล์ เมษายน 2019.csv
ตัวเลือกนี้จะสร้างคิวรีใหม่ที่มีขั้นตอนการนําทางไปยังไฟล์นั้นโดยตรงเป็นไบนารีและชื่อของคิวรีใหม่นี้คือเส้นทางไฟล์ของไฟล์ที่เลือก เปลี่ยนชื่อคิวรีนี้เพื่อให้ ไฟล์ตัวอย่าง
สร้างพารามิเตอร์ใหม่ ที่มีชื่อ พารามิเตอร์ไฟล์ และชนิดของ ไบนารี ใช้คิวรี ของไฟล์ตัวอย่าง เป็น ค่าเริ่มต้น และ ค่าปัจจุบัน
โน้ต
เราขอแนะนําให้คุณอ่านบทความเกี่ยวกับ พารามิเตอร์ เพื่อให้เข้าใจวิธีการสร้างและจัดการพารามิเตอร์ใน Power Query ได้ดียิ่งขึ้น
ฟังก์ชันแบบกําหนดเองสามารถสร้างขึ้นโดยใช้ชนิดพารามิเตอร์ต่าง ๆ ไม่มีข้อกําหนดสําหรับฟังก์ชันแบบกําหนดเองใดๆ เพื่อให้มีไบนารีเป็นพารามิเตอร์
ชนิดพารามิเตอร์ไบนารีจะแสดงภายในกล่องโต้ตอบ พารามิเตอร์ชนิด เมนูแบบเลื่อนลงเมื่อคุณมีคิวรีที่ประเมินเป็นไบนารี
คุณสามารถสร้างฟังก์ชันแบบกําหนดเองโดยไม่มีพารามิเตอร์ได้ ซึ่งมักจะเห็นได้ในสถานการณ์ที่การป้อนข้อมูลสามารถอนุมานได้จากสภาพแวดล้อมที่มีการเรียกใช้ฟังก์ชัน ตัวอย่างเช่น ฟังก์ชันที่ใช้วันที่และเวลาปัจจุบันของสภาพแวดล้อม และสร้างสตริงข้อความเฉพาะจากค่าเหล่านั้น
คลิกขวา พารามิเตอร์ไฟล์ จากบานหน้าต่าง คิวรี่ เลือกตัวเลือก อ้างอิง
เปลี่ยนชื่อคิวรีที่สร้างขึ้นใหม่จากพารามิเตอร์ไฟล์ (2) เป็น ไฟล์ตัวอย่างการแปลง
คลิกขวาที่คิวรี แปลงไฟล์ใหม่นี้ แล้วเลือกตัวเลือก สร้างฟังก์ชัน
การดําเนินการนี้จะสร้างฟังก์ชันใหม่ที่เชื่อมโยงกับไฟล์ตัวอย่างการแปลง คิวรี การเปลี่ยนแปลงใดๆ ที่คุณทํากับไฟล์ตัวอย่างการแปลง คิวรีจะถูกจําลองแบบอัตโนมัติไปยังฟังก์ชันแบบกําหนดเองของคุณ ในระหว่างการสร้างฟังก์ชันใหม่นี้ ให้ใช้ แปลงแฟ้ม เป็นชื่อฟังก์ชัน
หลังจากที่คุณสร้างฟังก์ชันแล้ว ให้สังเกตว่ามีการสร้างกลุ่มใหม่ให้กับคุณด้วยชื่อของฟังก์ชันของคุณแล้ว กลุ่มใหม่นี้ประกอบด้วย:
- พารามิเตอร์ทั้งหมดที่ถูกอ้างอิงในไฟล์ตัวอย่างการแปลง ของคุณ คิวรี
- แปลงไฟล์ตัวอย่างของคุณ คิวรี ซึ่งมักเรียกว่าคิวรีตัวอย่าง
- ฟังก์ชันที่สร้างขึ้นใหม่ของคุณ ในกรณีนี้ ไฟล์การแปลงไฟล์
ใช้การแปลงข้อมูลกับคิวรีตัวอย่าง
เมื่อสร้างฟังก์ชันใหม่ของคุณแล้ว ให้เลือกคิวรีที่มีชื่อ ไฟล์ตัวอย่างการแปลง คิวรีนี้จะถูกเชื่อมโยงกับ แปลงไฟล์ ฟังก์ชัน ดังนั้นการเปลี่ยนแปลงใดๆ ที่เกิดขึ้นกับคิวรีนี้จะปรากฏในฟังก์ชัน การเชื่อมต่อนี้เรียกว่าแนวคิดของคิวรีตัวอย่างที่เชื่อมโยงไปยังฟังก์ชัน
การแปลงครั้งแรกที่จําเป็นต้องเกิดขึ้นกับคิวรีนี้คือการแปลงไบนารี คุณสามารถคลิกขวาที่ไบนารีจากบานหน้าต่างแสดงตัวอย่าง และเลือกตัวเลือก CSV เพื่อแปลไบนารีเป็นไฟล์ CSV
รูปแบบของไฟล์ CSV ทั้งหมดในโฟลเดอร์เหมือนกัน พวกเขาทั้งหมดมีส่วนหัวที่ครอบคลุมสี่แถวแรกบนสุด ส่วนหัวของคอลัมน์จะอยู่ในแถว 5 และข้อมูลเริ่มต้นจากแถวที่ 6 ลงไปด้านล่าง ดังที่แสดงในภาพถัดไป
ชุดถัดไปของขั้นตอนการแปลงที่จําเป็นต้องนําไปใช้กับไฟล์ตัวอย่างการแปลง คือ:
ลบแถวสี่แถวบนสุด—การดําเนินการนี้จะกําจัดแถวที่ถือว่าเป็นส่วนหนึ่งของส่วนส่วนหัวของไฟล์
โน้ต
เมื่อต้องการเรียนรู้เพิ่มเติมเกี่ยวกับวิธีการลบแถวหรือกรองตารางตามตําแหน่งแถว ไปที่ กรองตามตําแหน่งแถว
เลื่อนระดับส่วนหัว— ส่วนหัวสําหรับตารางขั้นสุดท้ายของคุณตอนนี้อยู่ในแถวแรกของตาราง คุณสามารถเลื่อนระดับคอลัมน์ได้ดังที่แสดงในรูปถัดไป
หลังจากเลื่อนระดับส่วนหัวของคอลัมน์ตามค่าเริ่มต้นแล้ว Power Query จะเพิ่ม เปลี่ยนประเภทใหม่โดยอัตโนมัติ ขั้นตอนที่ตรวจหาชนิดข้อมูลสําหรับแต่ละคอลัมน์โดยอัตโนมัติ คิวรี แปลงไฟล์ตัวอย่าง ของคุณมีลักษณะเหมือนรูปภาพถัดไป
โน้ต
เมื่อต้องการเรียนรู้เพิ่มเติมเกี่ยวกับวิธีการเลื่อนระดับและลดระดับส่วนหัว ให้ไปที่ เลื่อนระดับหรือลดระดับส่วนหัวของคอลัมน์
ความระมัดระวัง
ฟังก์ชัน แปลงไฟล์ ของคุณขึ้นอยู่กับขั้นตอนที่ดําเนินการในคิวรี Transform Sample อย่างไรก็ตาม ถ้าคุณพยายามปรับเปลี่ยนรหัสสําหรับไฟล์ที่แปลง ด้วยตนเอง ฟังก์ชัน คุณจะพบคําเตือนที่อ่าน 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 ในตัวอย่างนี้) ภายในแท็บ เพิ่มคอลัมน์ ในริบบอน ให้เลือก เรียก ฟังก์ชันแบบกําหนดเอง จากกลุ่ม ทั่วไป ของ ภายในหน้าต่าง เรียกฟังก์ชันแบบกําหนดเองของ ให้ใส่ ตารางผลลัพธ์ เป็นชื่อคอลัมน์ใหม่ เลือกชื่อของฟังก์ชันของคุณ ไฟล์แปลง จากรายการดรอปดาวน์คิวรี Function หลังจากที่คุณเลือกฟังก์ชันจากเมนูดรอปดาวน์ แล้ว พารามิเตอร์สําหรับฟังก์ชันจะแสดงขึ้น และคุณสามารถเลือกคอลัมน์จากตารางที่จะใช้เป็นอาร์กิวเมนต์สําหรับฟังก์ชันนี้ได้ เลือกคอลัมน์ Content เป็นค่า / อาร์กิวเมนต์ที่จะส่งผ่านสําหรับพารามิเตอร์ไฟล์
หลังจากที่คุณเลือก ตกลงคอลัมน์ใหม่ที่มีชื่อ ตารางผลลัพธ์จะถูกสร้างขึ้น คอลัมน์นี้มี ค่าตาราง ในเซลล์ ดังที่แสดงในรูปถัดไป เพื่อความง่าย ให้ลบคอลัมน์ทั้งหมดออกจากตารางนี้ ยกเว้น ชื่อ และ ตารางผลลัพธ์
โน้ต
เมื่อต้องการเรียนรู้เพิ่มเติมเกี่ยวกับวิธีการเลือกหรือลบคอลัมน์ออกจากตาราง ไปที่ เลือกหรือลบคอลัมน์
ฟังก์ชันของคุณถูกนําไปใช้กับทุกแถวจากตารางโดยใช้ค่าจากคอลัมน์ Content เป็นอาร์กิวเมนต์สําหรับฟังก์ชันของคุณ เมื่อข้อมูลถูกแปลงเป็นรูปร่างที่คุณกําลังค้นหาแล้ว คุณสามารถขยายคอลัมน์ ผลลัพธ์ตาราง โดยการเลือกไอคอนขยายได้ อย่าใช้คํานําหน้าคอลัมน์ที่ขยาย
คุณสามารถตรวจสอบว่าคุณมีข้อมูลจากไฟล์ทั้งหมดในโฟลเดอร์โดยตรวจสอบค่าในคอลัมน์ Name หรือ Date สําหรับกรณีนี้ คุณสามารถตรวจสอบค่าจากคอลัมน์ วันที่ เนื่องจากแต่ละไฟล์มีข้อมูลสําหรับเดือนเดียวเท่านั้นจากปีที่ระบุ ถ้าคุณเห็นมากกว่าหนึ่งไฟล์ นั่นหมายความว่า คุณได้รวมข้อมูลจากหลายไฟล์ลงในตารางเดียวเรียบร้อยแล้ว
โน้ต
สิ่งที่คุณอ่านจนถึงตอนนี้เป็นพื้นฐานของกระบวนการเดียวกับที่เกิดขึ้นระหว่างการ รวมไฟล์ ประสบการณ์ แต่ทําด้วยตนเอง
เราขอแนะนําให้คุณอ่านบทความในภาพรวมของ รวมไฟล์ และ รวมไฟล์ CSV เพื่อทําความเข้าใจเพิ่มเติมว่าประสบการณ์การรวมไฟล์ทํางานใน Power Query อย่างไร และบทบาทที่ฟังก์ชันแบบกําหนดเองเล่น
เพิ่มพารามิเตอร์ใหม่ลงในฟังก์ชันแบบกําหนดเองที่มีอยู่
สมมติว่ามีข้อกําหนดใหม่เกี่ยวกับสิ่งที่คุณสร้างขึ้นในขณะนี้ ข้อกําหนดใหม่กําหนดว่า ก่อนที่คุณจะรวมไฟล์ คุณกรองข้อมูลภายในไฟล์เพื่อรับเฉพาะแถวที่ ประเทศ เท่ากับ Panama
เมื่อต้องการทําให้ข้อกําหนดนี้เกิดขึ้น ให้สร้างพารามิเตอร์ใหม่ที่เรียกว่า Market ด้วยชนิดข้อมูลข้อความ สําหรับ ค่าปัจจุบันของให้ป้อนค่า Panama
ด้วยพารามิเตอร์ใหม่นี้ เลือกไฟล์ตัวอย่างการแปลง คิวรี และกรองเขตข้อมูล Country โดยใช้ค่าจากพารามิเตอร์ Market
โน้ต
เมื่อต้องการเรียนรู้เพิ่มเติมเกี่ยวกับวิธีการกรองคอลัมน์ตามค่า ไปที่ ค่าตัวกรอง
การนําขั้นตอนนี้ไปใช้กับคิวรีของคุณจะอัปเดตไฟล์การแปลง โดยอัตโนมัติ ฟังก์ชัน ซึ่งตอนนี้ต้องใช้พารามิเตอร์สองตัวที่ยึดตามพารามิเตอร์สองตัวที่ ไฟล์ตัวอย่างการแปลง ใช้
แต่ไฟล์ CSV คิวรีมีเครื่องหมายคําเตือนอยู่ถัดจาก ในตอนนี้ฟังก์ชันของคุณได้รับการอัปเดตแล้ว จึงจําเป็นต้องใช้สองพารามิเตอร์ ดังนั้นขั้นตอนที่คุณเรียกใช้ฟังก์ชันจะส่งผลให้มีค่าผิดพลาด เนื่องจากหนึ่งในอาร์กิวเมนต์ถูกส่งผ่านไปยังไฟล์การแปลง ฟังก์ชันในระหว่างการ เรียกใช้ฟังก์ชันแบบกําหนดเอง ขั้นตอน
หากต้องการแก้ไขข้อผิดพลาด ให้ดับเบิลคลิก เรียกใช้ฟังก์ชันแบบกําหนดเอง ในหน้าต่าง ขั้นตอนที่ใช้ เพื่อเปิดหน้าต่าง เรียกฟังก์ชันแบบกําหนดเอง ของ ในพารามิเตอร์ Market ให้ป้อนค่าด้วยตนเอง Panama
ตอนนี้คุณสามารถกลับไปยัง ตารางผลลัพธ์ที่ขยายแล้ว ในขั้นตอนที่ใช้ ตรวจสอบคิวรีของคุณเพื่อตรวจสอบว่ามีเฉพาะแถวที่ ประเทศ เท่ากับ ปานามา แสดงในชุดผลลัพธ์สุดท้ายของคิวรี CSV Files
สร้างฟังก์ชันแบบกําหนดเองจากตรรกะที่สามารถนํามาใช้ใหม่ได้
หากคุณมีคิวรีหรือค่าหลายรายการที่ต้องการการแปลงชุดเดียวกัน คุณสามารถสร้างฟังก์ชันแบบกําหนดเองที่ทําหน้าที่เป็นตรรกะที่นํามาใช้ใหม่ได้ ต่อมา ฟังก์ชันแบบกําหนดเองนี้สามารถเรียกใช้กับคิวรีหรือค่าที่คุณเลือกได้ ฟังก์ชันแบบกําหนดเองนี้สามารถประหยัดเวลาและช่วยคุณในการจัดการชุดการแปลงของคุณในตําแหน่งที่ตั้งส่วนกลาง ซึ่งคุณสามารถปรับเปลี่ยนได้ตลอดเวลา
ตัวอย่างเช่น ลองนึกถึงคิวรีที่มีหลายรหัสเป็นสตริงข้อความ และคุณต้องการสร้างฟังก์ชันที่ถอดรหัสค่าเหล่านั้น เช่นเดียวกับในตารางตัวอย่างต่อไปนี้:
รหัส |
---|
PTY-CM1090-LAX |
LAX-CM701-PTY |
PTY-CM4441-MIA |
MIA-UA1257-LAX |
LAX-XY2842-MIA |
คุณเริ่มต้นด้วยการมีพารามิเตอร์ที่มีค่าที่ทําหน้าที่เป็นตัวอย่าง สําหรับกรณีนี้ นี่คือค่า PTY-CM1090-LAX
จากพารามิเตอร์นั้น คุณสร้างคิวรีใหม่ที่คุณนําการแปลงที่คุณต้องการไปใช้ สําหรับกรณีนี้ คุณต้องการแยกรหัส PTY-CM1090-LAX ออกเป็นหลายคอมโพเนนต์:
- จุดเริ่มต้น = PTY
- ปลายทาง = LAX
- สายการบิน = 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
จากนั้นคุณสามารถแปลงคิวรีนั้นให้เป็นฟังก์ชัน โดยการคลิกขวาบนคิวรี และเลือก สร้างฟังก์ชัน ในตอนท้าย คุณสามารถเรียกใช้ฟังก์ชันแบบกําหนดเองของคุณลงในคิวรีหรือค่าใด ๆ ของคุณ ดังที่แสดงในรูปภาพถัดไป
หลังจากการแปลงเพิ่มเติมเล็กน้อย คุณจะเห็นว่าคุณได้ผลลัพธ์ที่คุณต้องการและใช้ตรรกะสําหรับการแปลงดังกล่าวจากฟังก์ชันแบบกําหนดเอง