แนวทางปฏิบัติที่ดีที่สุดเมื่อทํางานกับ Power Query

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

เลือกตัวเชื่อมต่อที่เหมาะสม

Power Query มีตัวเชื่อมต่อข้อมูลจํานวนมาก ตัวเชื่อมต่อเหล่านี้มีตั้งแต่แหล่งข้อมูล เช่น ไฟล์ TXT, CSV และ Excel ไปจนถึงฐานข้อมูล เช่น Microsoft SQL Server และบริการ SaaS ที่ได้รับความนิยม เช่น Microsoft Dynamics 365 และ Salesforce ถ้าคุณไม่เห็นแหล่งข้อมูลของคุณแสดงอยู่ในหน้าต่าง รับข้อมูล คุณสามารถใช้ตัวเชื่อมต่อ ODBC หรือ OLEDB เพื่อเชื่อมต่อกับแหล่งข้อมูลของคุณได้เสมอ

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

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

ตัวกรองในช่วงต้น

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

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

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

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

ดําเนินงานที่มีราคาแพง

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

การดําเนินการอื่น ๆ (เช่น ตัวกรอง) ไม่จําเป็นต้องอ่านข้อมูลทั้งหมดก่อนที่จะส่งกลับผลลัพธ์ใดๆ แต่ฟังก์ชันเหล่านี้ทํางานผ่านข้อมูลในสิ่งที่เรียกว่ารูปแบบ "การสตรีม" แทน ข้อมูล "สตรีม" โดย และผลลัพธ์จะถูกส่งกลับไปพร้อมกัน ในตัวแก้ไข Power Query การดําเนินการดังกล่าวจําเป็นต้องอ่านเพียงพอของข้อมูลต้นฉบับเพื่อรวบรวมตัวอย่าง

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

ทํางานชั่วคราวกับชุดย่อยของข้อมูลของคุณ

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

ใช้ชนิดข้อมูลที่ถูกต้อง

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

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

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

สํารวจข้อมูลของคุณ

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

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

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

คุณยังสามารถโต้ตอบกับคุณลักษณะเหล่านี้ซึ่งช่วยให้คุณเตรียมข้อมูลของคุณ

บันทึกงานของคุณเป็นเอกสาร

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

ในขณะที่ Power Query สร้างชื่อขั้นตอนสําหรับคุณโดยอัตโนมัติในบานหน้าต่างขั้นตอนที่ใช้ คุณยังสามารถเปลี่ยนชื่อขั้นตอนของคุณ หรือเพิ่มคําอธิบายไปยังขั้นตอนใด ๆ ได้

ใช้วิธีการแบบแยกส่วน

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

ตัวอย่างเช่น สมมติว่าคุณมีคิวรีที่มีเก้าขั้นตอนที่แสดงในรูปต่อไปนี้

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

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

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

สร้างกลุ่ม

วิธีที่ยอดเยี่ยมในการจัดระเบียบงานของคุณคือ การใช้กลุ่มในบานหน้าต่างคิวรี

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

พยายามตั้งชื่อที่สื่อความหมายให้กับกลุ่มของคุณซึ่งเหมาะสมกับคุณและกรณีของคุณ

คิวรีที่พิสูจน์อักษรในอนาคต

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

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

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

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

  หมายเหตุ

  หากต้องการเรียนรู้เพิ่มเติมเกี่ยวกับการกรองข้อมูลของคุณตามตําแหน่งแถว ให้ไปที่ กรองตารางตามตําแหน่งแถว

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

  หมายเหตุ

  หากต้องการเรียนรู้เพิ่มเติมเกี่ยวกับการเลือกหรือลบคอลัมน์ ออก ให้ไปที่ เลือกหรือเอาคอลัมน์ออก

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

  หมายเหตุ

  หากต้องการเรียนรู้เพิ่มเติมเกี่ยวกับตัวเลือกในการเปลี่ยนคอลัมน์แนวตั้งเป็นแนวนอน ให้ไปที่ เปลี่ยนคอลัมน์แนวนอนเป็นแนวตั้ง

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

  หมายเหตุ

  เมื่อต้องการเพิ่มเติมเกี่ยวกับการทํางานและการจัดการกับข้อผิดพลาด ให้ไปที่ การจัดการกับข้อผิดพลาด

ใช้พารามิเตอร์

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

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

  

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

  

ประโยชน์หลักของการสร้างและการใช้พารามิเตอร์คือ:

• มุมมองแบบรวมศูนย์ของพารามิเตอร์ทั้งหมดของคุณผ่านหน้าต่างจัดการพารามิเตอร์

  

• การใช้พารามิเตอร์ในหลายขั้นตอนหรือหลายคิวรีกลับมาใช้ใหม่

• ทําให้การสร้างฟังก์ชันแบบกําหนดเองเป็นไปอย่างง่ายดายและตรงไปตรงมา

คุณยังสามารถใช้พารามิเตอร์ในอาร์กิวเมนต์บางอย่างของตัวเชื่อมต่อข้อมูล ตัวอย่างเช่น คุณสามารถสร้างพารามิเตอร์สําหรับชื่อเซิร์ฟเวอร์ของคุณเมื่อเชื่อมต่อกับฐานข้อมูล SQL Server ของคุณได้ จากนั้นคุณสามารถใช้พารามิเตอร์นั้นภายในกล่องโต้ตอบฐานข้อมูล SQL Server

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

สร้างฟังก์ชันที่นํามาใช้ใหม่ได้

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

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

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

คุณเริ่มต้นด้วยการมีพารามิเตอร์ที่มีค่าที่ทําหน้าที่เป็นตัวอย่าง

จากพารามิเตอร์นั้น คุณสร้างคิวรีใหม่ที่คุณนําการแปลงที่คุณต้องการไปใช้ สําหรับกรณีนี้ คุณต้องการแยกรหัส PTY-CM1090-LAX ออกเป็นหลายคอมโพเนนต์:

• จุดเริ่มต้น = PTY
• ปลายทาง = LAX
• สายการบิน = CM
• รหัสเที่ยวบิน = 1090

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

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