คุณอาจคุ้นเคยกับคิวรีพารามิเตอร์ที่มีการใช้งานใน SQL หรือ Microsoft Query อย่างไรก็ตาม พารามิเตอร์Power Queryมีความแตกต่างที่สําคัญ:
-
พารามิเตอร์สามารถใช้ในขั้นตอนคิวรีใดๆ นอกเหนือจากการทํางานเป็นตัวกรองข้อมูลแล้วยังสามารถใช้พารามิเตอร์เพื่อระบุสิ่งต่างๆ เช่น เส้นทางไฟล์หรือชื่อเซิร์ฟเวอร์ได้
-
พารามิเตอร์ไม่พร้อมท์สําหรับการป้อนข้อมูล แต่คุณสามารถเปลี่ยนค่าได้อย่างรวดเร็วโดยใช้Power Query คุณสามารถจัดเก็บและรับค่าจากเซลล์ใน Excel ได้
-
พารามิเตอร์จะถูกบันทึกในแบบสอบถามพารามิเตอร์อย่างง่าย แต่แยกออกจากแบบสอบถามข้อมูลที่ใช้ในแบบสอบถาม เมื่อสร้างแล้ว คุณสามารถเพิ่มพารามิเตอร์ลงในคิวรีได้ตามต้องการ
หมาย เหตุ ถ้าคุณต้องการวิธีอื่นในการสร้างคิวรีพารามิเตอร์ ให้ดู สร้างคิวรีพารามิเตอร์ใน Microsoft Query
คุณสามารถใช้พารามิเตอร์เพื่อเปลี่ยนค่าในคิวรีโดยอัตโนมัติ และหลีกเลี่ยงการแก้ไขคิวรีทุกครั้งเพื่อเปลี่ยนค่า คุณเพียงแค่เปลี่ยนค่าพารามิเตอร์ เมื่อคุณสร้างพารามิเตอร์ พารามิเตอร์นั้นจะถูกบันทึกไว้ในคิวรีพารามิเตอร์พิเศษซึ่งคุณสามารถเปลี่ยนได้โดยตรงจาก Excel ได้อย่างสะดวก
-
เลือก ข้อมูล > รับข้อมูล > แหล่งข้อมูลอื่น > เปิดใช้ ตัวแก้ไข Power Query
-
ในตัวแก้ไข Power Query ให้เลือก หน้าแรก > จัดการพารามิเตอร์ > พารามิเตอร์ใหม่
-
ในกล่องโต้ตอบ จัดการพารามิเตอร์ ให้เลือก ใหม่
-
ตั้งค่าต่อไปนี้ตามต้องการ:
ชื่อ
ซึ่งควรแสดงถึงฟังก์ชันของพารามิเตอร์ แต่ให้สั้นที่สุดเท่าที่จะเป็นไปได้
คำอธิบาย
ซึ่งอาจมีรายละเอียดใดๆ ที่จะช่วยให้ผู้คนใช้พารามิเตอร์ได้อย่างถูกต้อง
จำเป็น
เลือกทําอย่างใดอย่างหนึ่งต่อไปนี้:
ค่าใดๆ คุณสามารถใส่ค่าใดๆ ของชนิดข้อมูลใดก็ได้ในคิวรีพารามิเตอร์ รายการของค่า คุณสามารถจํากัดค่าให้กับรายการที่ระบุได้โดยการใส่ค่าลงในเส้นตารางขนาดเล็ก คุณต้องเลือก ค่าเริ่มต้น และ ค่าปัจจุบัน ที่ด้านล่าง แบบ สอบ ถาม เลือกคิวรีรายการ ซึ่งคล้ายกับคอลัมน์ที่มีโครงสร้าง รายการ ซึ่งคั่นด้วยเครื่องหมายจุลภาคและวงเล็บปีกกา ตัวอย่างเช่น เขตข้อมูลสถานะปัญหาอาจมีสามค่า: {"ใหม่", "ต่อเนื่อง", "ปิด"} คุณต้องสร้างคิวรีรายการล่วงหน้าโดยการเปิดเครื่องมือแก้ไขขั้นสูง (เลือก หน้าแรก > เครื่องมือแก้ไขขั้นสูง) เอาเทมเพลตโค้ดออก ใส่รายการค่าในรูปแบบรายการคิวรี แล้วเลือก เสร็จสิ้น เมื่อคุณสร้างพารามิเตอร์เสร็จแล้ว คิวรีรายการจะแสดงในค่าพารามิเตอร์ของคุณชนิด
ซึ่งจะระบุชนิดข้อมูลของพารามิเตอร์
ค่าที่แนะนํา
ถ้าต้องการ ให้เพิ่มรายการของค่าหรือระบุคิวรีเพื่อให้คําแนะนําสําหรับการป้อนข้อมูล
ค่าเริ่มต้น
ซึ่งจะปรากฏขึ้นเฉพาะเมื่อ ค่าที่แนะนํา ถูกตั้งค่าเป็น รายการค่า และระบุว่าข้อมูลในรายการใดเป็นค่าเริ่มต้น ในกรณีนี้ คุณต้องเลือกค่าเริ่มต้น
ค่าปัจจุบัน
ถ้าคิวรีนี้ว่างเปล่า คิวรีอาจไม่ส่งกลับผลลัพธ์ใดๆ ทั้งนี้ขึ้นอยู่กับตําแหน่งที่คุณใช้พารามิเตอร์ ถ้าเลือก จําเป็นค่าปัจจุบัน ต้องไม่ว่างเปล่า
-
เมื่อต้องการสร้างพารามิเตอร์ ให้เลือก ตกลง
ต่อไปนี้เป็นวิธีการจัดการการเปลี่ยนแปลงตําแหน่งที่ตั้งแหล่งข้อมูลและช่วยป้องกันข้อผิดพลาดในการรีเฟรช ตัวอย่างเช่น สมมติว่า Schema และแหล่งข้อมูลที่คล้ายกัน ให้สร้างพารามิเตอร์เพื่อเปลี่ยนแหล่งข้อมูลได้อย่างง่ายดาย และช่วยป้องกันไม่ให้เกิดข้อผิดพลาดในการรีเฟรชข้อมูล ในบางครั้ง เซิร์ฟเวอร์ ฐานข้อมูล โฟลเดอร์ ชื่อไฟล์ หรือตําแหน่งที่ตั้งจะเปลี่ยนไป บางครั้งตัวจัดการฐานข้อมูลอาจสลับเซิร์ฟเวอร์ การปล่อยไฟล์ CSV รายเดือนจะไปยังโฟลเดอร์อื่น หรือคุณต้องการสลับระหว่างสภาพแวดล้อมการพัฒนา/ทดสอบ/การผลิตอย่างง่ายดาย
ขั้นตอนที่ 1: สร้างคิวรีพารามิเตอร์
ในตัวอย่างต่อไปนี้ คุณมีไฟล์ CSV หลายไฟล์ที่คุณนําเข้าโดยใช้การดําเนินการนําเข้าโฟลเดอร์ (เลือก ข้อมูล > รับ> จากไฟล์ > จากโฟลเดอร์) จากโฟลเดอร์ C:\DataFilesCSV1 แต่บางครั้งโฟลเดอร์อื่นจะถูกใช้เป็นตําแหน่งที่ตั้งในการวางไฟล์ C:\DataFilesCSV2 คุณสามารถใช้พารามิเตอร์ในคิวรีเป็นค่าทดแทนสําหรับโฟลเดอร์อื่นได้
-
เลือก หน้าแรก > จัดการพารามิเตอร์ > พารามิเตอร์ใหม่
-
ใส่ข้อมูลต่อไปนี้ในกล่องโต้ตอบ จัดการพารามิเตอร์
ชื่อ
CSVFileDrop
คำอธิบาย
ตําแหน่งที่ตั้งสําหรับปล่อยไฟล์สํารอง
จำเป็น
ใช่
ชนิด
ข้อความ
ค่าที่แนะนํา
ค่าใดๆ
ค่าปัจจุบัน
C:\DataFilesCSV1
-
เลือก ตกลง
ขั้นตอนที่ 2: เพิ่มพารามิเตอร์ลงในคิวรีข้อมูล
-
เมื่อต้องการตั้งค่าชื่อโฟลเดอร์เป็นพารามิเตอร์ ใน การตั้งค่าคิวรี ภายใต้ ขั้นตอนคิวรี ให้เลือก แหล่งข้อมูล แล้วเลือก แก้ไขการตั้งค่า
-
ตรวจสอบให้แน่ใจว่าตัวเลือก เส้นทางไฟล์ ถูกตั้งค่าเป็น พารามิเตอร์ แล้วเลือกพารามิเตอร์ที่คุณเพิ่งสร้างขึ้นจากรายการดรอปดาวน์
-
เลือก ตกลง
ขั้นตอนที่ 3: อัปเดตค่าพารามิเตอร์
ตําแหน่งที่ตั้งของโฟลเดอร์เพิ่งเปลี่ยนแปลง ดังนั้นตอนนี้คุณสามารถอัปเดตคิวรีพารามิเตอร์ได้
-
เลือก ข้อมูล > การเชื่อมต่อ &คิวรี>คิวรี ให้คลิกขวาที่คิวรีพารามิเตอร์ แล้วเลือก แก้ไข
-
ใส่ตําแหน่งที่ตั้งใหม่ในกล่อง ค่าปัจจุบัน เช่น C:\DataFilesCSV2
-
เลือก หน้าแรก > ปิดการโหลด&
-
เมื่อต้องการยืนยันผลลัพธ์ของคุณ ให้เพิ่มข้อมูลใหม่ลงในแหล่งข้อมูล แล้วรีเฟรชคิวรีข้อมูลด้วยพารามิเตอร์ที่อัปเดต (เลือก ข้อมูล > รีเฟรชทั้งหมด)
ในบางครั้งคุณต้องการวิธีง่ายๆ ในการเปลี่ยนตัวกรองของคิวรีเพื่อรับผลลัพธ์ที่แตกต่างกันโดยไม่ต้องแก้ไขคิวรีหรือสร้างสําเนาที่แตกต่างกันเล็กน้อยของคิวรีเดียวกัน ในตัวอย่างนี้ เราเปลี่ยนวันที่เพื่อการเปลี่ยนแปลงตัวกรองข้อมูลได้อย่างสะดวก
-
เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือก คิวรี > แก้ไข สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel
-
เลือกลูกศรตัวกรองในส่วนหัวของคอลัมน์ใดๆ เพื่อกรองข้อมูลของคุณ แล้วเลือกคําสั่งตัวกรอง เช่น ตัวกรองวันที่/เวลา > หลังจาก กล่องโต้ตอบ กรองแถว จะปรากฏขึ้น
-
เลือกปุ่มทางด้านซ้ายของกล่อง ค่า แล้วเลือกทําอย่างใดอย่างหนึ่งต่อไปนี้:
-
เมื่อต้องการใช้พารามิเตอร์ที่มีอยู่ ให้เลือก พารามิเตอร์ แล้วเลือกพารามิเตอร์ที่คุณต้องการจากรายการที่ปรากฏทางด้านขวา
-
เมื่อต้องการใช้พารามิเตอร์ใหม่ ให้เลือก พารามิเตอร์ใหม่ แล้วสร้างพารามิเตอร์
-
-
ใส่วันที่ใหม่ในกล่อง ค่าปัจจุบัน แล้วเลือก หน้าแรก > ปิด& โหลด
-
เมื่อต้องการยืนยันผลลัพธ์ของคุณ ให้เพิ่มข้อมูลใหม่ลงในแหล่งข้อมูล แล้วรีเฟรชคิวรีข้อมูลด้วยพารามิเตอร์ที่อัปเดต (เลือก ข้อมูล > รีเฟรชทั้งหมด) ตัวอย่างเช่น เปลี่ยนค่าตัวกรองเป็นวันที่อื่นเพื่อดูผลลัพธ์ใหม่
-
ใส่วันที่ใหม่ในกล่อง ค่าปัจจุบัน
-
เลือก หน้าแรก > ปิดการโหลด&
-
เมื่อต้องการยืนยันผลลัพธ์ของคุณ ให้เพิ่มข้อมูลใหม่ลงในแหล่งข้อมูล แล้วรีเฟรชคิวรีข้อมูลด้วยพารามิเตอร์ที่อัปเดต (เลือก ข้อมูล > รีเฟรชทั้งหมด)
ในตัวอย่างนี้ ค่าในพารามิเตอร์คิวรีจะถูกอ่านจากเซลล์ในเวิร์กบุ๊กของคุณ คุณไม่จําเป็นต้องเปลี่ยนคิวรีพารามิเตอร์ คุณเพียงแค่อัปเดตค่าในเซลล์ ตัวอย่างเช่น คุณต้องการกรองคอลัมน์ตามตัวอักษรตัวแรก แต่เปลี่ยนค่าเป็นตัวอักษรจาก A เป็น Z ได้อย่างง่ายดาย
-
บนเวิร์กชีตในเวิร์กบุ๊กที่มีการโหลดคิวรีที่คุณต้องการกรอง ให้สร้างตาราง Excel ที่มีเซลล์สองเซลล์: ส่วนหัวและค่า
MyFilter
G
-
เลือกเซลล์ในตาราง Excel แล้วเลือก ข้อมูล > รับ>ข้อมูลจากตาราง/ช่วง ตัวแก้ไข Power Queryจะปรากฏขึ้น
-
ในกล่อง ชื่อ ของบานหน้าต่าง การตั้งค่าคิวรี ทางด้านขวา ให้เปลี่ยนชื่อคิวรีให้มีความหมายมากขึ้น เช่น FilterCellValue
-
เมื่อต้องการส่งผ่านค่าในตาราง ไม่ใช่ตัวตาราง ให้คลิกขวาที่ค่าในการแสดงตัวอย่างข้อมูล แล้วเลือก ดูรายละเอียดแนวลึก
โปรดสังเกตว่าสูตรได้เปลี่ยนเป็น = #"Changed Type"{0}[MyFilter]
เมื่อคุณใช้ตาราง Excel เป็นตัวกรองในขั้นตอนที่ 10 Power Queryจะอ้างอิงค่า ตาราง เป็นเงื่อนไขตัวกรอง การอ้างอิงโดยตรงไปยังตาราง Excel จะทําให้เกิดข้อผิดพลาด
-
เลือก >หน้าแรกปิดการโหลด&>ปิดการโหลด& ขณะนี้คุณมีพารามิเตอร์คิวรีที่ชื่อว่า "FilterCellValue" ที่คุณใช้ในขั้นตอนที่ 12
-
ในกล่องโต้ตอบ นําเข้าข้อมูล ให้เลือก สร้างเฉพาะการเชื่อมต่อ เท่านั้น แล้วเลือก ตกลง
-
เปิดคิวรีที่คุณต้องการกรองด้วยค่าในตาราง FilterCellValue คิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query โดยการเลือกเซลล์ในข้อมูล แล้วเลือก คิวรี > แก้ไข สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel
-
เลือกลูกศรตัวกรองในส่วนหัวของคอลัมน์ใดๆ เพื่อกรองข้อมูลของคุณ แล้วเลือกคําสั่งตัวกรอง เช่น ตัวกรองข้อความ > เริ่มต้นด้วย กล่องโต้ตอบ กรองแถว จะปรากฏขึ้น
-
ใส่ค่าใดๆ ในกล่อง ค่า เช่น "G" แล้วเลือก ตกลง ในกรณีนี้ ค่าคือพื้นที่ที่สํารองไว้ชั่วคราวสําหรับค่าในตาราง FilterCellValue ที่คุณใส่ในขั้นตอนถัดไป
-
เลือกลูกศรทางด้านขวาของแถบสูตรเพื่อแสดงสูตรทั้งหมด ต่อไปนี้เป็นตัวอย่างของเงื่อนไขตัวกรองในสูตร:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G")) -
เลือกค่าของตัวกรอง ในสูตร ให้เลือก "G"
-
ใช้ M Intellisense ใส่ตัวอักษรสองสามตัวแรกของตาราง FilterCellValue ที่คุณสร้าง แล้วเลือกจากรายการที่ปรากฏ
-
เลือก >หน้าแรกปิด>ปิด&โหลด
ผล
ขณะนี้คิวรีของคุณใช้ค่าในตาราง Excel ที่คุณสร้างเพื่อกรองผลลัพธ์คิวรี เมื่อต้องการใช้ค่าใหม่ ให้แก้ไขเนื้อหาของเซลล์ในตาราง Excel ต้นฉบับในขั้นตอนที่ 1 เปลี่ยน "G" เป็น "V" แล้วรีเฟรชคิวรี
คุณสามารถควบคุมว่าจะอนุญาตหรือไม่อนุญาตแบบสอบถามพารามิเตอร์
-
ในตัวแก้ไข Power Query ให้เลือก ตัวเลือก>ไฟล์ และ การตั้งค่า > ตัวเลือกคิวรี >ตัวแก้ไข Power Query
-
ในบานหน้าต่างด้านซ้าย ภายใต้ GLOBAL ให้เลือก ตัวแก้ไข Power Query
-
ในบานหน้าต่างทางด้านขวา ภายใต้ พารามิเตอร์ ให้เลือกหรือล้าง อนุญาตการกําหนดพารามิเตอร์ในกล่องโต้ตอบแหล่งข้อมูลและการแปลงเสมอ