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

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

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

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

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

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

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

2. ในตัวแก้ไข Power Query ให้เลือก Home>Manage Parameters > New Parameters

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

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

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

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

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

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

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

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

1. เลือก Home>Manage ParametersNew Parameters>

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. ในบานหน้าต่างทางด้านขวา ภายใต้ พารามิเตอร์ ให้เลือกหรือล้าง อนุญาตการกําหนดพารามิเตอร์ในกล่องโต้ตอบแหล่งข้อมูลและการแปลงเสมอ

ดูเพิ่มเติม

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

ใช้พารามิเตอร์แบบสอบถาม (docs.com)