การสร้างแบบสอบถามพารามิเตอร์ (Power Query)

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

1. เลือก ข้อมูล > รับข้อมูล > แหล่งข้อมูลอื่น > เปิดใช้ ตัวแก้ไข Power Query

2. ในตัวแก้ไข Power Query ให้เลือก หน้าแรก > จัดการพารามิเตอร์ > พารามิเตอร์ใหม่

3. ในกล่องโต้ตอบ จัดการพารามิเตอร์ ให้เลือก ใหม่

4. ตั้งค่าต่อไปนี้ตามต้องการ:

   ชื่อ	ซึ่งควรแสดงถึงฟังก์ชันของพารามิเตอร์ แต่ให้สั้นที่สุดเท่าที่จะเป็นไปได้
   คำอธิบาย	ซึ่งอาจมีรายละเอียดใดๆ ที่จะช่วยให้ผู้คนใช้พารามิเตอร์ได้อย่างถูกต้อง
   จำเป็น	เลือกทําอย่างใดอย่างหนึ่งต่อไปนี้: ค่าใดๆ คุณสามารถใส่ค่าใดๆ ของชนิดข้อมูลใดก็ได้ในคิวรีพารามิเตอร์ รายการของค่า    คุณสามารถจํากัดค่าให้กับรายการที่ระบุได้โดยการใส่ค่าลงในเส้นตารางขนาดเล็ก คุณต้องเลือก ค่าเริ่มต้น และ ค่าปัจจุบัน ที่ด้านล่าง แบบ สอบ ถาม เลือกคิวรีรายการ ซึ่งคล้ายกับคอลัมน์ที่มีโครงสร้าง รายการ ซึ่งคั่นด้วยเครื่องหมายจุลภาคและวงเล็บปีกกา ตัวอย่างเช่น เขตข้อมูลสถานะปัญหาอาจมีสามค่า: {"ใหม่", "ต่อเนื่อง", "ปิด"} คุณต้องสร้างคิวรีรายการล่วงหน้าโดยการเปิดเครื่องมือแก้ไขขั้นสูง (เลือก หน้าแรก > เครื่องมือแก้ไขขั้นสูง) เอาเทมเพลตโค้ดออก ใส่รายการค่าในรูปแบบรายการคิวรี แล้วเลือก เสร็จสิ้น เมื่อคุณสร้างพารามิเตอร์เสร็จแล้ว คิวรีรายการจะแสดงในค่าพารามิเตอร์ของคุณ
   ชนิด	ซึ่งจะระบุชนิดข้อมูลของพารามิเตอร์
   ค่าที่แนะนํา	ถ้าต้องการ ให้เพิ่มรายการของค่าหรือระบุคิวรีเพื่อให้คําแนะนําสําหรับการป้อนข้อมูล
   ค่าเริ่มต้น	ซึ่งจะปรากฏขึ้นเฉพาะเมื่อ ค่าที่แนะนํา ถูกตั้งค่าเป็น รายการค่า และระบุว่าข้อมูลในรายการใดเป็นค่าเริ่มต้น ในกรณีนี้ คุณต้องเลือกค่าเริ่มต้น
   ค่าปัจจุบัน	ถ้าคิวรีนี้ว่างเปล่า คิวรีอาจไม่ส่งกลับผลลัพธ์ใดๆ ทั้งนี้ขึ้นอยู่กับตําแหน่งที่คุณใช้พารามิเตอร์ ถ้าเลือก จําเป็นค่าปัจจุบัน ต้องไม่ว่างเปล่า

5. เมื่อต้องการสร้างพารามิเตอร์ ให้เลือก ตกลง

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

ขั้นตอนที่ 1: สร้างคิวรีพารามิเตอร์

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

1. เลือก หน้าแรก > จัดการพารามิเตอร์ > พารามิเตอร์ใหม่

2. ใส่ข้อมูลต่อไปนี้ในกล่องโต้ตอบ จัดการพารามิเตอร์

   ชื่อ	CSVFileDrop
   คำอธิบาย	ตําแหน่งที่ตั้งสําหรับปล่อยไฟล์สํารอง
   จำเป็น	ใช่
   ชนิด	ข้อความ
   ค่าที่แนะนํา	ค่าใดๆ
   ค่าปัจจุบัน	C:\DataFilesCSV1

3. เลือก ตกลง

ขั้นตอนที่ 2: เพิ่มพารามิเตอร์ลงในคิวรีข้อมูล

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

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

3. เลือก ตกลง

ขั้นตอนที่ 3: อัปเดตค่าพารามิเตอร์

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

1. เลือก ข้อมูล > การเชื่อมต่อ &คิวรี>คิวรี ให้คลิกขวาที่คิวรีพารามิเตอร์ แล้วเลือก แก้ไข

2. ใส่ตําแหน่งที่ตั้งใหม่ในกล่อง ค่าปัจจุบัน เช่น C:\DataFilesCSV2

3. เลือก หน้าแรก > ปิดการโหลด&

4. เมื่อต้องการยืนยันผลลัพธ์ของคุณ ให้เพิ่มข้อมูลใหม่ลงในแหล่งข้อมูล แล้วรีเฟรชคิวรีข้อมูลด้วยพารามิเตอร์ที่อัปเดต (เลือก ข้อมูล > รีเฟรชทั้งหมด)

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

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือก คิวรี > แก้ไข สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel

2. เลือกลูกศรตัวกรองในส่วนหัวของคอลัมน์ใดๆ เพื่อกรองข้อมูลของคุณ แล้วเลือกคําสั่งตัวกรอง เช่น ตัวกรองวันที่/เวลา > หลังจาก กล่องโต้ตอบ กรองแถว จะปรากฏขึ้น
   
   

3. เลือกปุ่มทางด้านซ้ายของกล่อง ค่า แล้วเลือกทําอย่างใดอย่างหนึ่งต่อไปนี้:

   • เมื่อต้องการใช้พารามิเตอร์ที่มีอยู่ ให้เลือก พารามิเตอร์ แล้วเลือกพารามิเตอร์ที่คุณต้องการจากรายการที่ปรากฏทางด้านขวา

   • เมื่อต้องการใช้พารามิเตอร์ใหม่ ให้เลือก พารามิเตอร์ใหม่ แล้วสร้างพารามิเตอร์

4. ใส่วันที่ใหม่ในกล่อง ค่าปัจจุบัน แล้วเลือก หน้าแรก > ปิด& โหลด

5. เมื่อต้องการยืนยันผลลัพธ์ของคุณ ให้เพิ่มข้อมูลใหม่ลงในแหล่งข้อมูล แล้วรีเฟรชคิวรีข้อมูลด้วยพารามิเตอร์ที่อัปเดต (เลือก ข้อมูล > รีเฟรชทั้งหมด) ตัวอย่างเช่น เปลี่ยนค่าตัวกรองเป็นวันที่อื่นเพื่อดูผลลัพธ์ใหม่

6. ใส่วันที่ใหม่ในกล่อง ค่าปัจจุบัน

7. เลือก หน้าแรก > ปิดการโหลด&

8. เมื่อต้องการยืนยันผลลัพธ์ของคุณ ให้เพิ่มข้อมูลใหม่ลงในแหล่งข้อมูล แล้วรีเฟรชคิวรีข้อมูลด้วยพารามิเตอร์ที่อัปเดต (เลือก ข้อมูล > รีเฟรชทั้งหมด)

ในตัวอย่างนี้ ค่าในพารามิเตอร์คิวรีจะถูกอ่านจากเซลล์ในเวิร์กบุ๊กของคุณ คุณไม่จําเป็นต้องเปลี่ยนคิวรีพารามิเตอร์ คุณเพียงแค่อัปเดตค่าในเซลล์ ตัวอย่างเช่น คุณต้องการกรองคอลัมน์ตามตัวอักษรตัวแรก แต่เปลี่ยนค่าเป็นตัวอักษรจาก A เป็น Z ได้อย่างง่ายดาย

1. บนเวิร์กชีตในเวิร์กบุ๊กที่มีการโหลดคิวรีที่คุณต้องการกรอง ให้สร้างตาราง Excel ที่มีเซลล์สองเซลล์: ส่วนหัวและค่า
    

   MyFilter
   G

2. เลือกเซลล์ในตาราง Excel แล้วเลือก ข้อมูล > รับ>ข้อมูลจากตาราง/ช่วง ตัวแก้ไข Power Queryจะปรากฏขึ้น

3. ในกล่อง ชื่อ ของบานหน้าต่าง การตั้งค่าคิวรี ทางด้านขวา ให้เปลี่ยนชื่อคิวรีให้มีความหมายมากขึ้น เช่น FilterCellValue 

4. เมื่อต้องการส่งผ่านค่าในตาราง ไม่ใช่ตัวตาราง ให้คลิกขวาที่ค่าในการแสดงตัวอย่างข้อมูล แล้วเลือก ดูรายละเอียดแนวลึก

   โปรดสังเกตว่าสูตรได้เปลี่ยนเป็น = #"Changed Type"{0}[MyFilter]

   เมื่อคุณใช้ตาราง Excel เป็นตัวกรองในขั้นตอนที่ 10 Power Queryจะอ้างอิงค่า ตาราง เป็นเงื่อนไขตัวกรอง การอ้างอิงโดยตรงไปยังตาราง Excel จะทําให้เกิดข้อผิดพลาด

5. เลือก >หน้าแรกปิดการโหลด&>ปิดการโหลด& ขณะนี้คุณมีพารามิเตอร์คิวรีที่ชื่อว่า "FilterCellValue" ที่คุณใช้ในขั้นตอนที่ 12

6. ในกล่องโต้ตอบ นําเข้าข้อมูล ให้เลือก สร้างเฉพาะการเชื่อมต่อ เท่านั้น แล้วเลือก ตกลง

7. เปิดคิวรีที่คุณต้องการกรองด้วยค่าในตาราง FilterCellValue คิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query โดยการเลือกเซลล์ในข้อมูล แล้วเลือก คิวรี > แก้ไข สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel

8. เลือกลูกศรตัวกรองในส่วนหัวของคอลัมน์ใดๆ เพื่อกรองข้อมูลของคุณ แล้วเลือกคําสั่งตัวกรอง เช่น ตัวกรองข้อความ > เริ่มต้นด้วย กล่องโต้ตอบ กรองแถว จะปรากฏขึ้น 

9. ใส่ค่าใดๆ ในกล่อง ค่า เช่น "G" แล้วเลือก ตกลง ในกรณีนี้ ค่าคือพื้นที่ที่สํารองไว้ชั่วคราวสําหรับค่าในตาราง FilterCellValue ที่คุณใส่ในขั้นตอนถัดไป

10. เลือกลูกศรทางด้านขวาของแถบสูตรเพื่อแสดงสูตรทั้งหมด ต่อไปนี้เป็นตัวอย่างของเงื่อนไขตัวกรองในสูตร:
    
    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

11. เลือกค่าของตัวกรอง ในสูตร ให้เลือก "G"

12. ใช้ M Intellisense ใส่ตัวอักษรสองสามตัวแรกของตาราง FilterCellValue ที่คุณสร้าง แล้วเลือกจากรายการที่ปรากฏ

13. เลือก >หน้าแรกปิด>ปิด&โหลด

ผล

ขณะนี้คิวรีของคุณใช้ค่าในตาราง Excel ที่คุณสร้างเพื่อกรองผลลัพธ์คิวรี เมื่อต้องการใช้ค่าใหม่ ให้แก้ไขเนื้อหาของเซลล์ในตาราง Excel ต้นฉบับในขั้นตอนที่ 1 เปลี่ยน "G" เป็น "V" แล้วรีเฟรชคิวรี

คุณสามารถควบคุมว่าจะอนุญาตหรือไม่อนุญาตแบบสอบถามพารามิเตอร์

1. ในตัวแก้ไข Power Query ให้เลือก ตัวเลือก>ไฟล์ และ การตั้งค่า > ตัวเลือกคิวรี >ตัวแก้ไข Power Query

2. ในบานหน้าต่างด้านซ้าย ภายใต้ GLOBAL ให้เลือก ตัวแก้ไข Power Query

3. ในบานหน้าต่างทางด้านขวา ภายใต้ พารามิเตอร์ ให้เลือกหรือล้าง อนุญาตการกําหนดพารามิเตอร์ในกล่องโต้ตอบแหล่งข้อมูลและการแปลงเสมอ