สร้างคิวรีพารามิเตอร์ (Power Query)

คุณอาจค่อนข้างคุ้นเคยกับคิวรีพารามิเตอร์ที่ใช้ใน SQL หรือ Microsoft Query อย่างไรก็ตาม พารามิเตอร์ Power Query มีความแตกต่างที่สําคัญ:

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

หมายเหตุ ถ้าคุณต้องการวิธีอื่นในการสร้างคิวรีพารามิเตอร์ ให้ดู สร้างคิวรีพารามิเตอร์ใน Microsoft Query

สร้างพารามิเตอร์

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

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

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

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

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

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

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

ใช้พารามิเตอร์เพื่อเปลี่ยนแหล่งข้อมูล

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

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

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

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

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

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

3. เลือก ตกลง

ขั้นตอนที่ 2: เพิ่มพารามิเตอร์ไปยังแบบสอบถามข้อมูล

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

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

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

1. เลือก การเชื่อมต่อข้อมูล>& แบบสอบถาม>แท็บแบบสอบถาม ให้คลิกขวาที่แบบสอบถามพารามิเตอร์ แล้วเลือกแก้ไข
2. ใส่ตําแหน่งที่ตั้งใหม่ในกล่อง ค่าปัจจุบัน เช่น C:\DataFilesCSV2
3. เลือก Home>Close & Load
4. เมื่อต้องการยืนยันผลลัพธ์ของคุณ ให้เพิ่มข้อมูลใหม่ลงในแหล่งข้อมูล แล้วรีเฟรชแบบสอบถามข้อมูลด้วยพารามิเตอร์ที่อัปเดตแล้ว (เลือกรีเฟรช ข้อมูล>ทั้งหมด)

ใช้พารามิเตอร์เพื่อกรองข้อมูล

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

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

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

   

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

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

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

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

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

7. เลือก Home>Close & Load

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

ใช้ค่าในเซลล์เพื่อกรองข้อมูล

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

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

   MyFilter
   G

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

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

4. เมื่อต้องการส่งผ่านค่าในตาราง ไม่ใช่ที่ตัวตาราง ให้คลิกขวาที่ค่าในการแสดงตัวอย่างข้อมูล จากนั้นเลือก ดูรายละเอียดแนวลึก
   สังเกตว่า สูตรเปลี่ยนเป็น = #"Changed Type"{0}[MyFilter]
   เมื่อคุณใช้ตาราง Excel เป็นตัวกรองในขั้นตอนที่ 10 Power Query จะอ้างอิงค่าตารางเป็นเงื่อนไขตัวกรอง การอ้างอิงโดยตรงไปยังตาราง Excel จะทําให้เกิดข้อผิดพลาด

5. เลือก Home>Close & Load Close>& Load To ขณะนี้คุณมีพารามิเตอร์แบบสอบถามที่ชื่อ "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. เลือก Home>ปิด>ปิด & โหลด

ผลลัพธ์

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

ควบคุมการใช้คิวรีพารามิเตอร์

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

1. ในตัวแก้ไข ตัวแก้ไข Power Query ให้เลือก ตัวเลือกไฟล์ >และ การตั้งค่า>ตัวเลือกคิว>รี ตัวแก้ไข Power Query
2. ในบานหน้าต่างทางด้านซ้าย ภายใต้ ส่วนกลาง ให้เลือก ตัวแก้ไข Power Query
3. ในบานหน้าต่างด้านขวา ภายใต้ พารามิเตอร์ ให้เลือกหรือล้าง อนุญาตการกําหนดพารามิเตอร์ในกล่องโต้ตอบแหล่งข้อมูลและการแปลงเสมอ

ดูเพิ่มเติม

ความช่วยเหลือ Power Query สำหรับ Excel

ใช้พารามิเตอร์คิวรี (docs.com)