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

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

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

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

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

1. ใช้ประสบการณ์การใช้งานตัวเชื่อมต่อเพื่อเชื่อมต่อกับข้อมูลของคุณที่อยู่ของตัวเชื่อมต่อ เมื่อคุณเลือกข้อมูลของคุณแล้ว ให้เลือกปุ่ม แปลงข้อมูล หรือ แก้ไข ซึ่งจะนําคุณไปยังประสบการณ์การใช้งาน Power Query
2. คลิกขวาที่พื้นที่ว่างในบานหน้าต่าง คิวรี่ ทางด้านซ้าย
3. เลือก คิวรีเปล่า
4. ในหน้าต่างคิวรีที่ว่างเปล่าใหม่ ให้เลือกเมนู หน้าแรก ของ จากนั้น ตัวแก้ไขขั้นสูง
5. แทนที่เทมเพลตด้วยฟังก์ชันแบบกําหนดเองของคุณ เช่น:

   let
    HelloWorld = () => ("Hello World")
   in
    HelloWorld
   

6. เลือก เสร็จสิ้น

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   

   โน้ต

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

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

   

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

คุณเริ่มต้นด้วยการมีพารามิเตอร์ที่มีค่าที่ทําหน้าที่เป็นตัวอย่าง สําหรับกรณีนี้ นี่คือค่า 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"

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

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